Pulling out a random record...

[speedphreak]

I'd like to pull a random record from a MySQL database. I've tried the following query:

$query = "SELECT * from consult ORDER BY rand() LIMIT 1";

This works in a sense that it doesn't return the first record in the table every time. However, it does select the last record every time. After further inspection, all the rand() function is doing is reversing the order of all the records. Is this normal behavior? Would I be better off just selecting the random record with PHP instead of using the MySQL function? Thanks for any help.

[Sxooter]

select * from tabler order by random() limit 1 works for me.

I'm using pgsql, but I can't see why it doesn't work for mysql here. Using order by random() can be very expensive on large tables, since the whole table gets randomized, THEN the single row gets picked.

If you know the number of rows (or a close approximation of it) you can generate a random number in php with rand(0,maxrows-1) then just select from table limit 1 offset $OFFSET (or, in mysql's dialiect: select from table limit $OFFSET,1)