Select a random row with MySQL:
SELECT column FROM table
ORDER BY RAND()
LIMIT 1
Select a random row with PostgreSQL:
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1
Select a random row with Microsoft SQL Server:
SELECT TOP 1 column FROM table
ORDER BY NEWID()
You may find that ORDER BY RAND()
in MySQL or ORDER BY RANDOM()
is PostgreSQL do not yield great performance on very large tables. Another approach you can take in that situation is to do two separate queries: a count and then an offset. Like this:
SELECT count(*) AS n FROM table
Then you pick a random number between 0 and n and use it as the OFFSET value:
SELECT column FROM table
LIMIT 1 OFFSET :randomValue
The LIMIT and OFFSET statements work in both MySQL and PostgreSQL, other database engines have similar functionality.
You'll have to pick that random value using your programming language of choice. This approach may not be that much faster so you should see how fast the original random query is compared to this approach.