SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot yukimushu's Avatar
    Join Date
    Feb 2005
    Location
    United Kingdom
    Posts
    183
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Ordering by top 5 results by random?

    Hey folks,

    Just wondering if it's possible to select the latest 5 results from the database (based on product id), but order those 5 results randomally? - or would it require a mix of both PHP and MySQL?

    Eg:

    SELECT * FROM products ORDER BY pid ASC LIMIT 5 - and then make mysql randomise those 5 results picked?

    Cheers!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, do the randomizing in php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot yukimushu's Avatar
    Join Date
    Feb 2005
    Location
    United Kingdom
    Posts
    183
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, do the randomizing in php
    alright, cheers buddy.

  4. #4
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    While it is certainly possible to randomize it with an SQL something like this, follow what Rudy said and randomize it from PHP for I think that is the best way to go about it:
    Code SQL:
    SELECT * FROM (SELECT * FROM products ORDER BY ID DESC  LIMIT 5) AS temp ORDER BY RAND();

    To Rudy:
    Rudy, what argument can we possibly give against the above SQL? Inefficient, unnecessary use of a subquery, not straightforward, or just plain hackish?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    oh, i suppose it's okay if you like it, i just don't like it

    also, LIMIT in a subquery didn't always work

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot yukimushu's Avatar
    Join Date
    Feb 2005
    Location
    United Kingdom
    Posts
    183
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kailash Badu View Post
    While it is certainly possible to randomize it with an SQL something like this, follow what Rudy said and randomize it from PHP for I think that is the best way to go about it:
    Code SQL:
    SELECT * FROM (SELECT * FROM products ORDER BY ID DESC  LIMIT 5) AS temp ORDER BY RAND();

    To Rudy:
    Rudy, what argument can we possibly give against the above SQL? Inefficient, unnecessary use of a subquery, not straightforward, or just plain hackish?
    That's cool, I didn't realise you could do that in mysql

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by yukimushu View Post
    That's cool, I didn't realise you could do that in mysql
    it's very version specific. LIMIT in a subquery was added part way through the 5.0 series, then taken out for a release or two because of a couple of serious bugs, then put back in. i personally wouldn't depend on it being there for applications where i don't have absolute control over where it runs.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


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
  •