SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    get 5 latest in random order

    hi all,

    Im trying to get the 5 latest records and order them randomly, but this doesnt seem to work - any ideas...

    select article_head
    FROM news
    WHERE article_status = '1'
    ORDER BY article_date DESC, RAND()
    LIMIT 5

    thanks all

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Of course it don't work, you cannot order by 2 methods at the same time.
    You have to do it in 2 times
    Code:
    select article_head 
    from (
      select article_head, article_date
      FROM news
      WHERE article_status = '1'
      order by article_date desc
      limit 5
    )
    order by rand()
    That way, you first get 5 latest articles in the sub query, and you make a random over that list of 5 articles.

  3. #3
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm,

    well thanks, but that doesnt actually work, i get a

    General error: Every derived table must have its own alias

    any other suggestions?

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    give the derived table an alias:

    Code:
    select article_head 
    from (
      select article_head, article_date
      FROM news
      WHERE article_status = '1'
      order by article_date desc
      limit 5
    ) as derivedtable
    order by rand()


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •