Select a random record from a database

You want to select a completely random record from a table.

SELECT cat, dog
FROM (SELECT cat, dog, RANDOM()
FROM foo ORDER BY 3) bar
LIMIT 1;

The sub SELECT in this query retrieves your desired columns from the table foo, adding the RANDOM() function to generate a column containing a random number for each record. The results are placed into a new temporary table bar which is sorted by the value of the random column. We use ORDER BY 3, because the random column is the third one in the table. If we were only selecting cat, random(), we would use ORDER BY 2.

The table will look like the following:


city | state | random
-----+-------+-------------------
PDX | OR | 0.0895038028664439
LAX | CA | 0.598029873612351
SEA | WA | 0.150338718737633
SFO | CA | 0.134321480586343


Then a single result is returned using LIMIT 1.