SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2007
    Posts
    81
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    How to select a group of random rows?

    Hi Guys,

    I'm trying to figure out how to pull a select number of random rows from a table but I also want to include rows which have a specfic value in one column as well.

    I know this is how you pull 10 random rows.

    SELECT * FROM table ORDER BY RAND() LIMIT 10

    Lets say out of 100 rows 2 of them have the column "flag" set to "1" where the other 98 are set to "0".

    Is there a way to randomly select 10 rows from that table but to always include in that select the 2 rows which have their "flag" set to 1.

    Thanks in advance!

    Mike

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Sure, you can make your ORDER BY clause more complex, so that it first sorts by whether the flag column is set to 1, and then sorts by RAND():

    SELECT * FROM table ORDER BY flag = 1, RAND() LIMIT 10
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2007
    Posts
    81
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    That how I approached it to begin with and I'm just getting 10 random rows back as if the first query were running?... None of the rows returned are ones where the flag = 1?

    Thanks,

    Mike

  4. #4
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why don't you use UNION instead ?

  5. #5
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select *
      from t
     order by flag = 1 desc,rand()
     limit 10

  6. #6
    SitePoint Enthusiast
    Join Date
    May 2007
    Posts
    81
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    That did it, thanks swampBoogie.

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2007
    Posts
    81
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Just to try and push it a little further, can you suggest a way on how I would randomly select the flagged rows as well. At the moment, the two "flagged" rows are always displayed in the same order.

    Thanks again

  8. #8
    SitePoint Enthusiast
    Join Date
    May 2007
    Posts
    81
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Actually, no scrap that, all appears to be working great, thanks


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
  •