Pete Freitag Pete Freitag

SELECT a random row with SQL

Updated on February 26, 2024
By Pete Freitag
databases

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



sql databases postgresql mysql sqlserver oracle select

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:

Discuss / Follow me on Twitter ↯

Comments

Fortunately additional application logic allows your app to be cross DB.
by Bryan F. Hogan on 09/14/2005 at 1:32:44 PM UTC
Indeed it does Bryan, but these will usually perform better. Like most things in programming there is a tradeoff.

Personally - I would go with application logic so my app would work across multiple databases in most cases. In some cases I would go with the db specific option.
by Pete Freitag on 09/14/2005 at 1:36:07 PM UTC
Agree, just adding more information to your post.
by Bryan F. Hogan on 09/14/2005 at 1:38:03 PM UTC
Thanks, I probably should have posted a bit about that tradeoff in the post - oh well its down here now.
by Pete Freitag on 09/14/2005 at 1:39:38 PM UTC
The Oracle one doesn't work; you need an inline view:

SELECT column FROM
(
SELECT column FROM table
ORDER BY dbms_random.value
)
WHERE rownum = 1
by Mark Murphy on 09/14/2005 at 3:42:29 PM UTC
Thanks Mark - updated.
by Pete Freitag on 09/14/2005 at 4:55:45 PM UTC
At least on MySQL (and I'd imagine the others) the ORDER BY RAND() method scales very poorly, since it requires not only generating a random number for every row in the table, but then also sorting those unindexed values. Get more than a couple thousand rows in your table and it's ridiculously slow.

Here's a link to a method (requiring application logic) that has much better performance with large sets: http://www.greggdev.com/web/articles.php?id=6 The examples are PHP (ick), but they're easily understandable.
by Barney on 09/15/2005 at 12:39:23 PM UTC
I have no problems using "SELECT * FROM table WHERE column ='value' AND rownum <= 1" in Oracle 9i.
I don´t need to make an inline view. That gives me the first row in the recordset :-)
by Casper on 10/17/2005 at 7:25:42 AM UTC
Thanks. The systax worked perfect on my Oracle 9.2, and I used it in one of my SQR reports.
by Shupei Wang on 11/21/2005 at 7:09:37 PM UTC
Hi guys,Thanx , it worked perfectly on my oracle database n retrived the rows i reqd for an update
by Latha on 11/28/2005 at 4:05:20 PM UTC
To get the second record on mysql or postgresql you can use LIMIT and OFFSET, eg:

SELECT col FROM tbl LIMIT 1 OFFSET 1
by Pete Freitag on 12/22/2005 at 8:53:51 AM UTC
It's a very helpful media which solves our difficult problem.
Really It helps me more!!!!!1
by Shivprakash on 01/10/2006 at 3:30:04 AM UTC
how can pull two records from mysql randomly
by hemant on 02/03/2006 at 11:29:05 PM UTC
In Teradata,we can get random records use the below given Query.

Select cols
From table
SAMPLE RANDOMIZED ALLOCATION 10

Thanks
Renu
by Renugopal D on 05/06/2006 at 8:52:24 AM UTC
Oracle also has a SAMPLE function, similar to Teradata mentioned above.
by Damien Conlon on 07/12/2006 at 8:26:46 AM UTC
Throug Query
I want a table in sql server in which third column contains the sum of 2nd column. For Example

Col1 Col2 Col3
A 1 1(Sum of 1 and 0)
B 2 3(Sum of 2 and 1)
C 4 6(Sum of 4 and 2)
D 5 9(Sum of 4 and 5)

Thanks in Advance
by Ashutosh Dixit on 08/03/2006 at 6:38:30 AM UTC
Thanks for the great mySQL syntax. I'm using this on an application with a nice, small database, and it works perfectly. Saves me a good chunk of application logic.

I appreciate it!
by Nathan Logan on 10/06/2006 at 4:42:58 PM UTC
whts the sql statement to select one record at a time.the next record want to see when we click next button.
by rakesh on 10/27/2006 at 11:59:22 PM UTC
Can anydody tell me how to delete all the duplicate rows in a table. The database in POSTGRESQL.
Thanks in advance
by Saravanan on 11/06/2006 at 2:12:16 AM UTC
How to select data in second row from a table in SQL Server 2000?
by Shailendra Soni on 11/13/2006 at 3:10:02 AM UTC
How to select data in second row from a table in SQL Server 2000?
by Shailendra Soni on 11/13/2006 at 3:11:46 AM UTC
How to delete one of the two duplicate rows
by AMIT KUMAR on 11/15/2006 at 1:07:57 AM UTC
How to create procedure using ( two tables from one database and two tables from another database) in ms-sql
by AMIT KUMAR on 11/15/2006 at 1:09:43 AM UTC
How to retrieve the third row from a table in sql server without using primary key or any other distinct key and dont use order.
by senthilkumar on 11/21/2006 at 11:07:38 PM UTC
Hi All,

I want to retrieve some fields like customer name,customer_number and the totals of the sold_qty,billed_fees per annum for different customers in a single row per customer. I am getting group by expression error when I try can anyone provide some updates?
by Samir on 12/11/2006 at 3:55:31 PM UTC
Your examples were very clear and (almost) just what I need to do.

In a small dataset I find that the same record as that just selected randomly can be randomly selected again.

How would you modify the random query (for MS SQL Server) to NOT INCLUDE the previously selected record on a new request.

In my use we randomly display a new image (redord) on refreshing the web-browser page (which causes the query to run.)

Thank you.
by Dan Kaufman on 12/18/2006 at 7:37:21 PM UTC
i want to delete last 2 records for a particular proposal from a proposal details table in orcale database.
can any one help me by giving suggetion or query?

thanks in advance
by umesh on 01/03/2007 at 11:35:11 PM UTC
The firebird solution above does not work because the rand() function returns the same number each time within a query. So rows are not randomly ordered.

Each time you run the script you will get the same set of rows.
by James on 01/04/2007 at 7:53:51 AM UTC
Need to pull every 100th row from table in MS Server 2000. We can't use incrementing id columns as deletions and sorting requirements would render those values useless.
Any ideas greatly appreciated.
by Jeff S. on 01/23/2007 at 11:15:29 AM UTC
how to retrive nth row from a table in sqlserver with out using top and cursor
by balaramesh.t on 01/25/2007 at 4:08:00 AM UTC
please give the answer for the below

I have a table
2. name of the table is constant. ex:CustomFields
3. but the columns names & no of colmuns differ.
4. as this table contains millions of rows, it is difficult to browse which column has atleast one value in its respective rows.
5. so find the columns which have atleast one value in it.
6. two sets of results i want: a) cols with atleast one value b) cols with out any value.
by sharu on 01/25/2007 at 1:01:05 PM UTC
I want to select some random records from MS Access Database.
How to select it.
Any help would be greatly appreciated.
Thank You.
by Rahul on 05/18/2007 at 11:59:04 PM UTC
Thanks for the SQL info on RAND() Functions. Much Appreciated.
by T Cox on 06/15/2007 at 2:29:08 PM UTC
Hi i want to select my row as column
i am dealing Medical Lab Information System. the table is given below

testname testvalue
-------- ---------
AC 21
PC 12

i want the table selection like the following

AC PC
21 12


Please give me solution..

thanks in advance..


my email:selvinm@gmail.com
by SELVIN on 07/05/2007 at 9:03:42 AM UTC
Thanks for the random row tips. I sometimes have a large number of records so I took your idea and expanded on it. Here's a generic app of how the logic should work. I created the sql with MySQL:

'Get the number of possible rows in the table
varcount = run "Select count(rowid) from table"

'then, in code, generate a random number between 0 to the rowcount
varOFFSET = rnd * varcount

'Then run your new select statement offsetting it by the random value
randomrow = run "SELECT column FROM table
LIMIT 1 OFFSET " & varOFFSET


I tested it and it improved performance greatly. I only have 5000 records and it helped a lot. I imagine with millions of records this would be much more valuable.
by zack on 07/24/2007 at 5:07:05 PM UTC
Hey Pete -

It's been a while! Looked up this post because I couldn't remember the DB2 syntax I contributed earlier -- come to find out, it doesn't work on DB2 v8.2 running on z/OS. I get an exception saying the RAND() function is non-deterministic, and as such, can't be used in an ORDER BY CLAUSE...

The work-around:

SELECT <column_name>, RAND() as IDX FROM <table_name> ORDER BY IDX FETCH FIRST 1 ROWS ONLY
by Timothy C. Fanelli on 07/30/2007 at 9:22:53 AM UTC
Thanks Tim! I've updated the example in the entry.
by Pete Freitag on 07/31/2007 at 10:58:39 AM UTC
That's great! Using SQL Server 2005, how would you select random rows via a function?
by chris on 08/09/2007 at 10:50:32 AM UTC
How to desplay data like blow throw Select statement.
empno =100
salary=1000
dept name=IT
------------
empno =100
salary=1000
dept name=IT
------------
empno =100
salary=1000
dept name=IT
------------
empno =100
salary=1000
by Kumar on 08/21/2007 at 7:17:54 AM UTC
If you want random results in ACCESS simply add SORT BY RND(id) to your SQL.

SELECT * FROM people ORDER BY RND(personID)

It's important you use a number column
by TravisO on 08/24/2007 at 7:34:25 AM UTC
a useful post for many..
I hav Q.. and trying to fd an answer.. I need all ur help:

I have a table in which I have result, defect,name as columns:

Name Result Defect
A P x
A P x1
B P X2

When defect is not null and defect!=' ', I select the rest of the defect
for a particular 'name'='A'

I get the below result

x
x1

i.e two rows, but I need this in a single row like:

x,x1

is this possible?

I tried with COALESCE function in T-SQL... here it goes below:

I need a confirmation whether its correct or not... cos I dont have sufficient permission to create this function in my DB...


CREATE FUNCTION dbo.UDF_getdefect
( @name varchar(1000) )
RETURNS varchar(1000)
AS
BEGIN
DECLARE @defect varchar(1000), @Delimiter char
SET @Delimiter = '+'
SELECT @defected = COALESCE(@defected + @Delimiter, ') + defect FROM
(Select defect
from table1
where defect is NOT NULL and defect != ' '
and name =@name) derived
where defect is NOT NULL and defectReportId != ' '
and name =@name
RETURN ( SELECT REPLACE(@defected,')+(',') + (') AS [Defects])
END

If I need the result of this function in another Select query can I use it as below?

(Select getDefect(name),name from table1) as defected
by Priya on 09/25/2007 at 11:35:49 PM UTC
How To see in SQL server six th row
by pavan garg on 09/27/2007 at 7:03:38 AM UTC
Thanks tim, and mark murphy !! you have done a great job, making things a lot easier for innumberable sql guys.
by chris on 10/19/2007 at 1:32:23 AM UTC
Solution for MSAccess:
select top 1 column from tabela order by RND(INT(NOW*id)-NOW*id)
"id" is the column auto_numeric primary key
;D
by Matheus Garcia on 11/09/2007 at 6:38:48 AM UTC
Hey thanks for that info
by vmrejas on 11/21/2007 at 8:39:07 PM UTC
Thanks a log... very good post.
by Tiago Braga on 11/22/2007 at 1:24:31 PM UTC
thanks for the great info. do you know how to select multiple random rows? not just a row. thanks
by Jimmy on 12/01/2007 at 5:40:04 PM UTC
does the (Limit) statement in mysql work in Microsoft sql server 2000
by benjamin blay on 12/31/2007 at 2:45:06 AM UTC
When people are lazy and stupid, it is duty by god to slap them as hard as possible. Public blogs should not allow unfiltered public comments. Idiotic, below-beginner, ultra-annoying- -and-maximally-undeserving- novice requests like, 'tell me how to select the top 5 rows' drag down everyone and should not only never be answered, but they should be deleted.

Another great solution to selecting a random row: count number of rows in the table, pick a random value from 0 to that count (using applic logic to do random), then fetch data on that row using "LIMIT ROW#,1". This is the only method that works flawlessly here, across hundreds of millions of rows, in production, on mysql 5.0 in a myisam table in under 100msec. The trick is to use the same WHERE clause on both the count and the pull.
by Michael on 01/14/2008 at 7:36:04 AM UTC
how would i go about randomly picking maybe 10 rows and having it save in another table?!?!?!?!
by Evan on 01/18/2008 at 5:21:21 AM UTC
select rownum,ename,sal from (select ename,sal from emp order by sal desc)
where rownum<=4
minus
select rownum,ename,sal from (select ename,sal from emp order by sal desc)
where rownum<=3
by Prakher on 02/01/2008 at 5:18:36 AM UTC
How to get fifth highest record from table using top command in sql.
by neha on 02/22/2008 at 12:29:20 AM UTC
How to get fifth highest record from table using top command in sql.
by neha on 02/22/2008 at 12:30:09 AM UTC
Hi this is very useful for me...thanks for posting this query.
by Raj on 02/26/2008 at 6:07:02 AM UTC