Select a random record from a database

Posted June 16, 2004 by Cronjob in PostgreSQL PgSQL

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

SELECT cat, dog
FROM foo ORDER BY 3) bar

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.

The Conversation

Follow the reactions below and share your own thoughts.