How to request a random row in SQL?

sql random
sverrejoh picture sverrejoh · Aug 21, 2008 · Viewed 458.4k times · Source

How can I request a random row (or as close to truly random as is possible) in pure SQL?

Answer

Yaakov Ellis picture Yaakov Ellis · Aug 21, 2008

See this post: SQL to Select a random row from a database table. It goes through methods for doing this in MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 and Oracle (the following is copied from that link):

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

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

Related questions

MySQL select 10 random rows from 600K rows fast

How can I best write a query that selects 10 rows randomly from a total of 600k?

Read More
Select n random rows from SQL Server table

I've got a SQL Server table with about 50,000 rows in it. I want to select about 5,000 of those rows at random. I've thought of a complicated way, creating a temp table with a "random number" column, copying my table into …

Read More
How to randomly select rows in SQL?

I am using MSSQL Server 2005. In my db, I have a table "customerNames" which has two columns "Id" and "Name" and approx. 1,000 results. I am creating a functionality where I have to pick 5 customers randomly every time. Can anyone tell …

Read More