SELECT a random row with SQL
By Pete Freitag
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
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
SELECT a random row with SQL was first published on September 14, 2005.
If you like reading about sql, databases, postgresql, mysql, sqlserver, oracle, or select then you might also like:
- SQL Reserved Key Words Checker Tool
- Order by NULL Values in MySQL, Postgresql and SQL Server
- INFORMATION_SCHEMA Support in MySQL, PostgreSQL
- Returning TOP N Records