#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    5
    Rep Power
    0

    Selecting a random row from a result list


    Is it possible to select a certain (randomly generated) row from a query. So if a query normally has 91 results, than I just want one single result (randomly picked).
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    SELECT * FROM
    (my query here [LIMIT 91 if applicable]) x
    ORDER BY RAND() LIMIT 1;
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Baconcutter
    So if a query normally has 91 results,
    when you say "normally" does this mean you are actually running the query and returning 91 rows somewhere, or do you want to ~not~ return them all, just one of them at most, ever?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    5
    Rep Power
    0
    Well r937 I would say BULLSEYE. I use a query in a combination with PHP. From PHP I sent a SQL-query to MySQL, which than has a number of rows he can return (count(*)). Than I'll use Rand(1,<number of returning rows>) to create a random number. And than fetch the row from the database equaling the number we just created. But if we could skip all of this and build it all in in one query than it would just be great. Thats why the review from cafelatte looks appealing, but I do not know if it returns just one row.

IMN logo majestic logo threadwatch logo seochat tools logo