Select a random record.

Contributor Icon Contributed by Cronjob Date Icon June 16, 2004  
Tag Icon Tagged: PostgreSQL PgSQL

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.

Previous recipe | Next recipe |
 
  • Anonymous
    Just a little bit easier:

    SELECT cat, dog
    FROM foo
    ORDER BY random()
    LIMIT 1;
  • Chad
    select .... order by random() limit 1
  • Anonymous
    <ul id="quote"><h6>Anonymous wrote:</h6>Just a little bit easier:

    SELECT cat, dog
    FROM foo
    ORDER BY random()
    LIMIT 1;</ul>
  • Anonymous
    select ... order by rand()
  • Anonymous
    THe reason the author did it this way is because its the fastest performance wise. You are right in stating that order by rand() is more compact when speaking about code but this example is all about speed now.
blog comments powered by Disqus