SQL to Select a random row from a database table

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

SELECT TOP 1 column FROM table
ORDER BY NEWID()

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

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

Fortunately additional application logic allows your app to be cross DB.

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.

Agree, just adding more information to your post.

Thanks, I probably should have posted a bit about that tradeoff in the post - oh well its down here now.

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

Thanks Mark - updated.

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.

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

Thanks. The systax worked perfect on my Oracle 9.2, and I used it in one of my SQR reports.

Hi guys,Thanx , it worked perfectly on my oracle database n retrived the rows i reqd for an update

To get the second record on mysql or postgresql you can use LIMIT and OFFSET, eg:

SELECT col FROM tbl LIMIT 1 OFFSET 1

It's a very helpful media which solves our difficult problem.
Really It helps me more!!!!!1

how can pull two records from mysql randomly

In Teradata,we can get random records use the below given Query.

Select cols
From table
SAMPLE RANDOMIZED ALLOCATION 10

Thanks
Renu

Oracle also has a SAMPLE function, similar to Teradata mentioned above.

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

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!

whts the sql statement to select one record at a time.the next record want to see when we click next button.

Can anydody tell me how to delete all the duplicate rows in a table. The database in POSTGRESQL.
Thanks in advance

How to select data in second row from a table in SQL Server 2000?

How to select data in second row from a table in SQL Server 2000?

How to delete one of the two duplicate rows

How to create procedure using ( two tables from one database and two tables from another database) in ms-sql

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.

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?

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.

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

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.

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.

how to retrive nth row from a table in sqlserver with out using top and cursor

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 want to select some random records from MS Access Database.
How to select it.
Any help would be greatly appreciated.
Thank You.

Thanks for the SQL info on RAND() Functions. Much Appreciated.

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]

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.

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

Thanks Tim! I've updated the example in the entry.

That's great! Using SQL Server 2005, how would you select random rows via a function?

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

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

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

How To see in SQL server six th row

Thanks tim, and mark murphy !! you have done a great job, making things a lot easier for innumberable sql guys.

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

Hey thanks for that info

Thanks a log... very good post.

thanks for the great info. do you know how to select multiple random rows? not just a row. thanks

does the (Limit) statement in mysql work in Microsoft sql server 2000

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.

how would i go about randomly picking maybe 10 rows and having it save in another table?!?!?!?!

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

How to get fifth highest record from table using top command in sql.

How to get fifth highest record from table using top command in sql.

Hi this is very useful for me...thanks for posting this query.