Select random row that exists in MySQL [duplicate]

Question

Possible Duplicate:
How to request a random row in SQL?

Currently Using:

$randomQuery = mysql_fetch_row(mysql_query("SELECT * FROM `table` WHERE `id` >= RAND() * (SELECT MAX(`id`) FROM `table`) LIMIT 1"));

Table Structure:

id:
 2 
 4
 5

I want to make sure it's selecting an existing row. For example, it shouldn't be able to use 1 or 3 in its randomizing function. Is there a way to do this in MySQL?

Answer 1

I think

SELECT * FROM table ORDER BY RAND() LIMIT 1

would do the trick.

See:

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

Answer 2

SELECT * FROM table ORDER BY RAND() LIMIT 1

Answer 3

SELECT * FROM `table` ORDER BY RAND() LIMIT 0,1;

http://akinas.com/pages/en/blog/mysql_random_row/

Answer 4

Your initial query does take care of the holes as it simply compares the id of a row to the random value you compute and doesn't try to fetch a row having a random id.

Its problem is that it gives you a non uniform probability : Any row following a big hole has a bigger probability of being selected.

That's the reason why it's suggested to use the mysql trick ORDER BY RAND() (see other answers) as the randomization will be quasi uniform.