SELECT a random row with SQL

There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic, but each database server requires different SQL syntax.

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()

Note: SQL Server also supports using ORDER BY rand() but it will not actually select a random row order, the results will always be the same.

Select a random row with IBM DB2

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Thanks Tim!

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Thanks Mark Murphy

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 a count and then offset. So it requires two queries, and works 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