SQL to Select a random row from a database table
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()
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
Feel free to post other example, variations, and SQL statements for other database servers in the comments.
Like this? Follow me ↯
Tweet Follow @pfreitagSQL to Select a random row from a database table 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
- Top 10 Reserved SQL Keywords
- Backwards LIKE Statements
- Sphinx - Open Source SQL Full Text Search Engine
- Updated SQL Reserved Words Checker
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.
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
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.
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 :-)
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
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
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
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
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!
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
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?
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.
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
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.
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.
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.
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.
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:[email protected]
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:[email protected]
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.
'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
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
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
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 [email protected]) derived
where defect is NOT NULL and defectReportId != ' '
and name [email protected]
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
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 [email protected]) derived
where defect is NOT NULL and defectReportId != ' '
and name [email protected]
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
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.
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
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