Random row grabs in MySQL

Anon

Hey there, I'm trying to do something like the following...

SELECT * FROM Content WHERE CategoryID='$Cat' LIMIT 10

However, I'm trying to grab 10 random rows from the db, not the first 10 it finds everytime. Any magical command to help this along?

Regards,

-- npoole

brandonschnell

i haven't tried it but how about:

SELECT * FROM Content WHERE CategoryID='$Cat' ORDER BY RAND() LIMIT 10

you may need to seed rand() if its getting predictable.

http://www.mysql.com/doc/M/a/Mathematical_functions.html

mp96brbj

I think you might have to include RAND() as a column (if the previous suggestion didn't work):

SELECT *,RAND() FROM Content Where CategoryID='$Cat' ORDER BY RAND() LIMIT 10

...and if the above didn't work either, try giving it an alias:

SELECT *,RAND() AS foo FROM Content Where CategoryID='$Cat' ORDER BY foo LIMIT 10

Anon

No no....

I think that Him want to select 10 random records, and not 10 random colummns...

Searching the mysql manual I find not functions to extract random records from a table

Anon

You could always implement the select statement 10 times:

count = 0;
while ($count <= 10)
{
"SELECT * FROM db ORDER BY RAND() LIMIT 1";
count = count +1;
}

[deleted]

Nope, rand() in the columns section gets evaualted for every result found, so it's useless.
just put
SELECT * FROM table ORDER BY rand() limit 10;

But tyhis only works in MySQL 3.23+

sepodati

Hmm..the manual used to list that you could do it by using rand() in the column, if you were using a version before 3.23. Hmm..they must of changed something...

---John Holmes...

[deleted]

-- quote from the MySQL manual --
You can't use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. In MySQL 3.23, you can
however do: SELECT FROM table_name ORDER BY RAND() This is useful to get a random sample of a set SELECT FROM table1,table2 WHERE
a=b AND c<d ORDER BY RAND() LIMIT 1000. Note that a RAND() in a WHERE clause will be re-evaluated every time the WHERE is executed.

-- end quote --