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
Comments
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:selvinm@gmail.com
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 =@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
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.