SQL to Select a random row from a database table

September 14, 2005
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()

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.



Related Entries

224 people found this page useful, what do you think?

Trackbacks

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.
If you have a table with many rows, you can make this query much faster. A simple SELECT * FROM table ORDER BY RAND() has to do a sort in memory- expensive. If you change the query to SELECT * FROM table WHERE RAND()>0.9 ORDER BY RAND() then you've reduced the overhead of the sort by 90%. This will have the overhead of many RAND() statements, but on DB2 mainframes, it is a much faster technique.
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 :-)
Hai, Ths s elango.I have tried the above query what u have given but its not working in my database. I m tried in Sql Server Enterprise Manager... It given the following error.. [Miscrosoft][ODBC Sql Server Driver][Sql Server]'RANDOM' is not a recoganized function name. Could u pls help me to resolve ths solution.. bye bye Excepting u r reply as soon as possible Elango.M
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
How to delete the duplicate values (if 2 rows there, delete one row)in single table
how to retrieve second record from a table without using the where clause
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
It's great info - but I can't get it working in my script using a MDB Database via ODBC
Just one more db missing, firebird. Select a random row with Firebird: 1) Install ib_udf (comes with the server) or at least the rand() function with this statement: DECLARE EXTERNAL FUNCTION rand RETURNS DOUBLE PRECISION BY VALUE ENTRY_POINT 'IB_UDF_rand' MODULE_NAME 'ib_udf'; 2)Now proceed in selecting a random row from the database: SELECT FIRST 1 * FROM country ORDER BY rand()
how can pull two records from mysql randomly
what about MsAccess databse, how to select random row?
hemant: just adjust the LIMIT clause: SELECT column FROM table ORDER BY RAND() LIMIT 2 will pull two rows.
How can I select a random record from a random table? Can anyone help?
Great article, very useful, it helped me avoid using stored proceedures and cursors for a specific solution
Is there any one know SQL Random function for NCR Teradata? thanks so much for reading this.
for Teradata, use the sample feature: select * from table_name sample 1 using an integer will return that many rows, using a decimal will return that percent (ie .01 will bring back a 1 percent sample of rows)
In Teradata,we can get random records use the below given Query. Select cols From table SAMPLE RANDOMIZED ALLOCATION 10 Thanks Renu
Is there a way to pull top 10 rows in Teradata? I know in other databases we dan do it. "SAMPLE" pulls the rows and then gets random sample, I'm trying to pull only top 10 rows and not spool the whole table. --thanx
Oracle also has a SAMPLE function, similar to Teradata mentioned above.
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 :-)
For PostgreSQL this worked much better: SELECT * FROM table OFFSET RANDOM() LIMIT 1;
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 to delete first 10 rows in sql 2000, can any one help me?
>>I want to delete first 10 rows in sql 2000, can any one help me? Delete Top 10 from [table_name]
To delete the first 10 rows in sql 2000/Sybase from table test_table (Follows the order of the clustered index on the table) set rowcount 10 delete from test_table
How do i delete first 'n' rows from a table on PL/SQL ??? ( I tried the following 2 options but it gave an error saying command not properly ended: "set rowcount 10 delete from test_table" "Delete Top 10 from [table_name]" ) Please Suggest ... Thanks, Shantanu
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!
Can anyone help me in "How to delete first 10 rows in db2 udb" Thank You Hill
whts the sql statement to select one record at a time.the next record want to see when we click next button.
Its greate.
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.
How to change the order of the columns in the created table?
How I can delete any random row from a table.
How to select top 10 records?
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?
welcome to the general support SQL forum for outsourcing companies (hey guys, remember that your boss is charging someone for your work. you're supposed to know things like this already! ;-)
how to get random values in SQL.
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.
To implement a row limit for a select or delete, try the following... it works in just about every database: truncate table tablename --10 Note: the --10 is an option specifying the row limit. You can then perform your select or delete statement after that and the results will be truncated at 10 rows (or however many you specify). For example: truncate table customers --10 select * from customers This will return the first 10 customers. Enjoy!
Hi, This is Ravi, need to know how to fetch the random rows in sybase. Thanks Ravi
hello guys what up! please help me to learn MySQL syntax at PHP
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.
DO NOT FOLLOW THE COMMENT FROM "Nonnamiss"! The TRUNCATE command will *delete* your data. The "-- 10" command is a SQL comment and will have no effect. Pete, I suggest you delete/mark this dangerous post.
how can i fatch specified no of rows starting from some row no. for eg. if i want to fatch records from 5th row to 10th row how can i do it.
Hi, I want to update one table in sybase but it is taking very long time as the table contains millions of records. The server is going down. Is there any way to do batch wise
oh my god, what is with you people, and people asking questions like how to select top 10 records, well maybe you write select top 10 ?!? are you serious ? are you programmers or housewives ? sorry for my bad english.
Hi, I have a table with 2 colums, and I wanted to get all the records in a single row (My SQL). Can any one help me on this.
<table cellpadding="0" cellspacing="0" border="0" bgcolor="#E8EFDA" width="100%" style="border-bottom:#669900 1px dashed; border-top:#669900 1px dashed"> <tr><td style="font-family:Verdana, Arial, Helvetica, sans-serif; font-size:11px; padding:10px; color:#996600"> <span style="color:#4A7B2D;"><b>here's the code for that</b></span><br /><br /> select table1.*, table2.* from table1, table2 where table1.id=table2.id</td> </tr> </table>
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
you can use WHERE clause to specify your selection ( Nfact i didn't understand your table ). After that Nphp do some loop stuff by using (while) to retrive those data from the returned array.
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
VERY handy! I used dbms_random.value and rownum < 11 in a three layer nested query to pull 10 random grouped records to populate a script for a nightly data population process. THANKS!
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
I would like to filter one row from a list of rows, does anyone know how i can do it? I am using mysql db
Thank you. TSQL query save me a lot of work.
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.
SQL query I have 3 laks recored in table t1.I want to retin first 100 recored and rest of records should be delted....can any one hlep.. ADVACED THANKS
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
i want to only 5th row in sql 2005 give me a query
does the (Limit) statement in mysql work in Microsoft sql server 2000
MS Access: select top 1 * from tablename order by rnd(id) id should be an autonumber field
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?!?!?!?!
Hi How to fetch the 4th record alone from the table using sql?
i have 4 tables, how i retrive data randomly from 4 tables in sql server 2000, please help thanx in advance
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
Very useful tip!
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.
Hi, I'm a housewife. How do I select rows 18-32 from a random selected table ordered by color?
Hi I want the sql query to display columns of different rows in a single row.The data base is given below. I want to make it in one row with all the details. Please help 1 Rahul Bose Male #13, Bangalore, 686691 Landphone-4452121252 1 Rahul Bose Male #13, Bangalore, 686691 Mobilephone2-558521252 1 Rahul Bose Male #13, Bangalore, 686691 Official_Email-TL@abc.com
How can we display duplicate elements along with duplicate element. Example: table name xxxx column name yyyy elements are jhon jhon vignesh sat sat Result should be jhon jhon sat sat
in a oracle table i have 10 rows ,i want only 6rows,how to retrive that, ( I WANT ONLY RANDOM DATA) NOT like rowid < 7 data ? how to get this ?
Q.(1)How we retrieve data from oracle database randomly; like at first i want to choose data from row 1 then row 5 then row 3. Q.(2)How I create auto increment in oracle like My Sql. Q.(3) How I edit data in PL/SQL
How to interchange to row to column to SQL database
how to apply this to a jump menu?
i want to delete 1st row from table in ms sql server 2000 but there is primary key so plz help me
i want to be able to google fors answer? how to do? tel me nows!
Please post me a query that would swap the contents of two columns with same datatype, of course.
Hello, I am kartik. i want to study material. help me. All SQL Study Material. plz All Information in my mail address send to you. Thank You Kartik Shah
Hello, I am using postgresql database. I want to select rownum as serial no. How i solve it Thank you in advance
hai iam vishnu,send a query for below i mentioned.. select second highest salary in a employee table
hai iam vishnu,send a query for below i mentioned.. select second highest salary rows in a employee table
Hotness thanks, this is exactly what I was looking for!
I have a table of 12000 records. I want to take out 100 random record from it and make a new table (Access). Plz help me how to do it.
no one here past the 10th post has asked a NEW question... you can't just demand that the OP or whoever reads this blog is going to do this specifically for you and then email it to you. "thanks in advance" doesn't do anything, either. its also very sad to see the same question with the numbers changed posted a million times - also with the answer posted about a dozen times. everyone should just atctually READ and tried what was already presented here instead of asking the same thing one more time... BUUUT - i do know some thing who can answer ALL of YOUR questions: google.com
I use this one in Oracle: select * from (select rownum rowix, i.* from my_table order by dbms_random.random) where rowix < 2
Thank you Pete!
plz tell me the sql query for the record >2and=<6 ,the identical records select in a single row .
Hello every one , plz help me i wana query for a the i have same records fourtime in the table so the requirment is four separated rows combine into single row plz reply with solution as soon as possible
hi, i'm using mysql.i want to select questions randomly from database.And for that i used rand().it is working.Now i want to select 40% image question and 60% without image question from the database using the rand().help me plz.
I use the random row in sql server code but it not work properly.
How do I write sql statement regardless of the DB platform (DB Independence) to select only 1 record?
i have a large Database of wallpaperswhen i use RAND() function its slow mysql Queries I USE this: $sql = "select * from wallpaper where wallpaperid order by rand() limit 3"; Some one give me this: $random = Rand() $sql = "select * from wallpaper where id=".$random." limit 3"; But its not work please optmize it for me
i have one student table which contains student_id, name and Marks. I want to retrieve the student name based on his marks. i mean i want to retrieve the second highest marks scorer name. plz help in this query. i need it in oracle. Thanks
i have one student table which contains student_id, name and Marks. I want to retrieve the student name based on his marks. i mean i want to retrieve the second highest marks scorer name. plz help in this query. i need it in oracle. Thanks
Thanks to the fellow who suggested this for mysql: WHERE RAND()>0.9 ORDER BY RAND() Nice idea, and can be adjusted based on table size. This, combined with the idea of creating a temp table of random primary keys and then joining had good results on a 50 Million row table.
how to select the top 5 rows from the tables in sybase
for Jibanananda Matheus Garcia wrote: 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 So for 100 random rows change the TOP 1 to TOP 100
to Kavitha select Marks, name from studenttable order by Marks asc try this one
dsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsf vdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsf
how can i generate random number ins MSSQL that are unique? i rily nid help on this one..
How to select a column value in single row
Try http://www.000webhost.com/99011.html I am now using it and don't seen any server problems 1500 MB of Disk Space, 100 GB Bandwidth Host your own domain (http://www.yourdomain.com) cPanel Powered Hosting (you will love it) Over 500 website templates ready to download Easy to use website builder Free POP3 Email Box with Webmail access FTP and Web based File Manager PHP, MySQL, Perl, CGI, Ruby. And many more..
How to get these details of table as fieldname, field type, field size, and maximum width of the values in the field, with single parameter as TableName & store in temp table? Any Idea? Thanks in Advance Haque
can any one help me please..... how to get row count in ms sql server with that i can keep for loop in my program...
This post really helped me allot. I was trying for the same logic since from 3 days. and now i got it. really good one
Although elegant, it still requires a scan of all rows.
A rather bad way of doing things. Check http://www.dasprids.de/blog/2008/06/07/fetching-random-rows-of-mysql-efficiently for a better way (requires a unique index): - get the number of rows - draw a random integer between 1 and $num_rows - get the row with the corresponding id
How do i select first 'a' rows from a table on PL/SQL ??? ( I tried the following 2 options but it gave an error saying command not properly ended: "set rowcount 10 delete from test_table" "select Top 10 from [table_name]" )
Thanks for the sql code it works great!
hellow , can any one help me that what is the tera data Equivalent functions Qualify, Position,Sample,width_bucket, Is null Functions in to Sql Server 2005.
Solution for Random in sql server 2005 is,, select TOP 1 first_name from employee order by newid()
Solution for Random in sql server 2005 is,, select TOP 1 first_name from employee order by newid()
Solution for Random in sql server 2005 is,, select TOP 1 first_name from employee order by newid()
how to select more than one value in single row, the coloum names are like feature1, feature2, feature3,feature4....etc
how to include table as a record int the table
aat more than one value in single row, the coloum names are like featu
I want to Select Recordsets randomly from a DB with where conditions . . . Please Give solution for me . . .
how can i select or delect a particular row from a table in my database using sql*plus
Thank you- Very helpful. Exactly what I needed for SQL Server.
Does anyone know how to "remember" a random selection so i can preform a second action on the same records? Using MS-SQLserver 2000
how to delete top n records in terdata?
I have database of having 20000 rows, I would like to display all the reocords but in random order. The scenario is like this when people search they should get diffrent set of records in first page. I am using MS .sql2005. Suggest the optimized SQL statement for the above scenario.
its nice to select random rows in MY Sql by using limit 2,3 But it was not help in updating of that random row(i want to update particular field).... please tell me how to update random selected row.
great sql, thanks for convering all types - helped me alot
How can i select value of a random column of a table? Please help. Is is possible to use column id in select statement instead of column names. "select columnid from table order by 1" like the order by 1 refer to first column.
I believe that your approach is not the fastest. A comparison of different approaches that can be found in the following post, shows that the ORDER BY RAND() is the worst possible option in MySQL: http://sillybits.wordpress.com/2009/08/24/fetching-random-rows-from-a-database-table/
I have a form pack.In this form contains 4 textbox.I entered datas to all the four textbox and stored in the database(sql). Now I have another form packcheck.In this form also contains 4 textbox.Now i entered datas to all the textbox. now i click the submit button it check the database. the datas are already present in the database then entered datas are updated else it display the message "the entered datas are not present in the database. (use the code aspx and c#). this is my questio,please help me. advance thanks. Please help me,it is very urgent.please mail to me.
Could anyone please let me know how to select random record from Informix 7? Thanks a lot.
Thank you, Pete. For your help. PD: Your little article help to management some data in Chile, South America
Thank You for your valuable contribution.

I have a question in here. (IN SQL) How to add and extra row at the top of a "select query request". For example: If I wish to have the headings in the row 1 of my select Procedure, How can I achieve that.

Thanks In Advance.
Pallav Vidwans
I have an oracle table with 16M rows. Even indexed on the column, it takes 2.5 minutes using the oracle query above. Much too slow.
I have many clients databases in a server. The database struture same for all the clients.
My question is how to select a table from all the databases
like select * from mytable where mytable in ....
How i can insert a column in a table from random selecting another table?
Somebody knows how can I do that in sqlitle?? on android
Access related SQL query tested with all suggested strings. But getting not a random recordset ! Any help ?
SELECT *
FROM table_name
WHERE field
IN (

SELECT field
FROM table
ORDER BY RAND( )
)
ORDER BY RAND( )
LIMIT 15 , 60
How if there condition? like
select coloum from table where coloum = 'id' order by rand();
Hi Murphy,

Thanks a lot - it worked for me and I am sure that it will be appreciated.

Thanks once again,
Piyush
How do i select at random from mysql and have he result arranged alphabetically?
Order by rand is very slow. There is a better way using User Defined Variables.
See http://www.mysqldiary.com/how-to-produce-random-rows-from-a-table/
Quote: {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

Author: Matheus Garcia}

Many thanks for this post.

Greetings Nebu
hi.
hoe to get the rows no 5 to 10, in sql server.
to get the rows no 5 to 10, in sql server

using between operator
select *from employees; in orcle 9I it will show 11 rows like
---------
11
---------
11
---------
but we have any
command to view
--------
more than 11 rows like 12+
--------
Select all employee names from emp table
but display records of employees in Upper Case who get sal>3000. HOW?
Thanks a lot!!!
hi, how to retrieve a record randomly based on primary key in mysql... and also is there any other method other than using limit to limit the records retrieved randomly?
how to select table expect a particular row ?
eg : a table contain 1,2,3,4,5,6

i want select expect 2 how will to select
dear casper,
The topics was about to get random value, u provided a solution that is for select first row.
can you please provide a solution to get random row from oracle db.
thank you!
I want to sort a data set in ascending order so that it should return letters first, then numbers.Here we can't use ASC fuction since it returns numbers first.
e.g Data set : 5,4,2,A,C,3,B

Answer shold be : A,B,C,2,3,4,5


Any idea regarding this?
hi friends,
how to fetch a record from a table knowing only the serch keyword and i don't know which column contains that keyword..
kindly someone provide me a solution
@anonymous
try fulltextsearch in sql
i have a table containing 20 columns and around 50,000 rows in it. i need to retrieve the data of 1000 rows at once in the output and when i scroll down to the last row i.e 1000th row then again i need to retrieve another 1000 rows and add it to the output using stored procedures.
if any one has any idea please post the query etc. to my mail id- its_nv_verma@yahoo.co.in
hi friends, how to fetch a record from a table knowing only the serch keyword and i don't know which column contains that keyword.. kindly someone provide me a solution
thanks i use this query which help me alot and reduce my work
i have a query on Firebird sql as

SELECT FIRST 1000 i."id", i."slug", i."what_they_do", i."does_well", i."last_improves" FROM "inspections" i LEFT JOIN "services" s on i."service_id" = s."id"
WHERE s."active" = 1 AND s."suspended" = 0 AND i."report_date" > '2005-04-01' ORDER BY RAND() ASC;

and it executes very slow ,
Please help..
How to Select a Row in Microsoft SQL Server Management Studio Express??? Thx in Advance
it works but i want only to select 1 row... how can i do that?
*PLEASE* add 1 of the most popular public-domain databases on the planet today: Sqlite
i want to delete distinct row like

stuno name
101 mandar
101 ram
so one 101 should be delete
pl reply
I also want to delete distinct row

please help me.ww.adnpost.com
I have a table like
id name score group
1 a 20 A
2 aa 25 B
3 ak 30 A
4 ah 28 A
5 aj 50 C
6 ad 29 B
7 aw 27 C
8 al 48 C
9 ay 12 A



I need single query to get Output:

id name topscore Group
4 ah 28 A
6 ad 29 B
5 aj 50 C

group wise top score with user details


plz help me.

Thanks in advance
I have a table like
id name score group
1 a 20 A
2 aa 25 B
3 ak 30 A
4 ah 28 A
5 aj 50 C
6 ad 29 B
7 aw 27 C
8 al 48 C
9 ay 12 A



I need single query to get Output:

id name topscore Group
4 ah 28 A
6 ad 29 B
5 aj 50 C

group wise top score with user details


plz help me.

Thanks in advance
INSERT INTO score_master(id name score group) VALUES ('1','a','20','A'),('2,aa','25','B'),('3','ak','30','A'),('4','ah','28','A'),('5','aj','50','C'),('6','ad','29','B'),('7','aw', 27','C),(8','al','48','C'),('9','ay','12','A)

we need to get Result as:
(4 ah 28 A) (6 ad 29 B) (5 aj 50 C)
"ORDER BY NEWID()" brilliant!
I have a query which results 10 records with two coloumns, now I want the sum of 4 rows in between to be displayed as one row..? Kindly help..
How to select a nth from a table
ORDER BY NEWID() Perfect solution!!!
Perfect Bro You saved my Time
Excellent solution!! I can resolve my problem in Oracle..

Thanks from Argentina...
Using of RAND() is awesome but can I select a random table from a mysql database without using RAND()?
plz help me...
will you tell me about sql query to get the random data from the ms access 07 database
will you tell me about sql query to get the random data from the ms access 07 database
I need some help!! A cell is located in the fourth column and the fifth row. What is it's table reference? 4E? 44? DE? or is it D5?
I need some help!! A cell is located in the fourth column and the fifth row. What is it's table reference? 4E? 44? DE? or is it D5?
Hey Bernice, WTF?!
For Access 2007 & 2010, where
X = number of desired records
Y = any type of fields you want to see entered successively as [field1], [field2], [field3], etc...
Z = a number field, preferably autonumber, or one that contains a uniquely different value for each record.

try this...
SELECT TOP X [Y]
FROM [tablename]
ORDER BY RND(INT(NOW*[Z])-NOW*[Z])

It works for me.
Thanks, saved my time -- needed to delete rand 200 recs. Worked without a problem.
Thanks, saved my time -- needed to delete rand 200 recs. Worked without a problem.
Hi,How to find 3rd minimum salary from salary column in SQL
Hi,How to find 3rd minimum salary from salary column in SQL
Hi,How to find 3rd minimum salary from salary column in SQL
Hi,How to find 3rd minimum salary from salary column in SQL
query sql to retrieve image from table in oracle
In case anyone is interested in Ingres you do it like this

SELECT top 1 column
FROM table
ORDER BY RANDOM()
In case anyone is interested in Ingres you do it like this

SELECT top 1 column
FROM table
ORDER BY RANDOM()
In case anyone is interested in Ingres you do it like this

SELECT top 1 column
FROM table
ORDER BY RANDOM()
In case anyone is interested in Ingres you do it like this

SELECT top 1 column
FROM table
ORDER BY RANDOM()
Using this method with MYSQL and a large table (4 million rows for example) will create very very poor performance indeed and a lot of cpu load. The better solution for large tables is as follows;

select * from mytable
where myautoincid between 100000 and 100020
order by rand()
limit 1

You simply choose a random number, add say 20 to get a nice spread (in this case 100000 and 100020). This is done in the program logic. You should avoid selecting large amounts of records in this set!

You then order your X records by RAND() and limit 1.

Not only have you randomly chosen your program logic number, but you also sort your results randomly too and limit 1 returning just the 1 row.

In this case "myautoincid" would be an auto-incrementing numeric field which pretty much all engines support. You would create an index on this field for quick selection.

So it works by your application picking X records from your table using a 'between' of two values on your auto-increment id field. The results are then returned in a random order and limited to 1.

This can also be easily adapted to other engines. Very simple, very effective, very quick and efficient without generating a lot of cpu load.
Using this method with MYSQL and a large table (4 million rows for example) will create very very poor performance indeed and a lot of cpu load. The better solution for large tables is as follows;

select * from mytable
where myautoincid between 100000 and 100020
order by rand()
limit 1

You simply choose a random number, add say 20 to get a nice spread (in this case 100000 and 100020). This is done in the program logic. You should avoid selecting large amounts of records in this set!

You then order your X records by RAND() and limit 1.

Not only have you randomly chosen your program logic number, but you also sort your results randomly too and limit 1 returning just the 1 row.

In this case "myautoincid" would be an auto-incrementing numeric field which pretty much all engines support. You would create an index on this field for quick selection.

So it works by your application picking X records from your table using a 'between' of two values on your auto-increment id field. The results are then returned in a random order and limited to 1.

This can also be easily adapted to other engines. Very simple, very effective, very quick and efficient without generating a lot of cpu load.
Thanks its very helpfull
Thanks its very helpfull
Just to make it clear and correct:

Queries like "select * from mytable where myautoincid between 100000 and 100020 order by rand() limit 1" and "SELECT * FROM table WHERE RAND()>0.9 ORDER BY RAND()" and "SELECT * FROM table WHERE someid=RAND(0,maxid)" as well as a few other examples posted here can return erroneously no row at all, if you start deleting from the table or use a gappy sequence. And even more importantly, some of those queries can become pretty biased (in terms of randomness).

In other words, randomness is not something you can poke around and still expect getting correct random results. For example, RAND()*RAND() is not an unbiased random number at all even though it is a multiply of two random numbers. Even RAND()+RAND() can exhibit problems regarding bias and coverage. One should take a much deeper step into computer shuffling algorithms, if unbiased/serious results are expected.
Just to make it clear and correct:

Queries like "select * from mytable where myautoincid between 100000 and 100020 order by rand() limit 1" and "SELECT * FROM table WHERE RAND()>0.9 ORDER BY RAND()" and "SELECT * FROM table WHERE someid=RAND(0,maxid)" as well as a few other examples posted here can return erroneously no row at all, if you start deleting from the table or use a gappy sequence. And even more importantly, some of those queries can become pretty biased (in terms of randomness).

In other words, randomness is not something you can poke around and still expect getting correct random results. For example, RAND()*RAND() is not an unbiased random number at all even though it is a multiply of two random numbers. Even RAND()+RAND() can exhibit problems regarding bias and coverage. One should take a much deeper step into computer shuffling algorithms, if unbiased/serious results are expected.
Just to make it clear and correct:

Many queries posted here can return erroneously no row at all, if you start deleting from the table or use a gappy sequence. And even more importantly, some of those queries can become pretty biased (in terms of randomness).

In other words, randomness is not something you can poke around and still expect getting correct random results. For example, RAND()*RAND() is not an unbiased random number at all even though it is a multiply of two random numbers. Even RAND()+RAND() can exhibit problems regarding bias and coverage. One should take a much deeper step into computer shuffling algorithms, if unbiased/serious results are expected.
This is unbelievably awful advice, sir. You're selecting the entire table, then shuffling it, then returning a single row.

Please remove this terrible advice, so that people can find advice from sources that are competant.
Similar results can be found on link http://cybosofttech.com/select-random-record-by-sql/ with example.
Similar results can be found on link http://cybosofttech.com/select-random-record-by-sql/ with example.
Similar results can be found on link cybosofttech.com/select-random-record-by-sql/ with example.
Similar results can be found on link cybosofttech.com/select-random-record-by-sql/ with example.
f7yEMO Hi! I'm just wondering if i can get in touch with you, since you have amazing content, and i'm thinking of running a couple co- projects! email me pls
I am final, I am sorry, but it at all does not approach me. Perhaps there are still variants? or http://www.youtube.com/watch?v=xvTEhz67EnM bsn syntha 6 or >:-(( or
i want code for sql to display headings in row not in column with different datatypes not same data type pls help me in this case.Reply to my request
i want code for sql to display headings in row not in column with different datatypes not same data type pls help me in this case.Reply to my request
i want code for sql to display headings in row not in column with different datatypes not same data type pls help me in this case.Reply to my request
i want code for sql to display headings in row not in column with different datatypes not same data type pls help me in this case.Reply to my request
i want code for sql to display headings in row not in column with different datatypes not same data type pls help me in this case.Reply to my request
IF RANDOM SINGLE ROW DISPLAY IS DESIRED. SQL2K5

SELECT TOP 1 * FROM EMPLOYEES
ORDER BY NEWID();
IF RANDOM SINGLE ROW DISPLAY IS DESIRED. SQL2K5

SELECT TOP 1 * FROM EMPLOYEES
ORDER BY NEWID();
thanks for this code, it helped me a lot!
thanks a lot.
sqlite :

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1
Thanks for this.
thank you!
thax for hlpng me
How to compare corresponding rows in a table????
this query was useful for, what we select that remaining data will display like random

select * from (table name) where (column name) < > ('value') order by newid()
Awesome functions, thanks!
Great article with very interesting information. You might want to follow up to this topic!?!
Great article with very interesting information. You might want to follow up to this topic!?!
Great one stop post.

I know people debate the usefulness of doing it at application or db, it's faster at the db rather than pull a random recordset on the application end. I also tend to do the heavy lifting at the database layer so that I can make things cross platform a lot easier. SQL code across DB's is a lot more interchangeable than C#, Objective C, PHP, etc. Then the only thing the application does is control display and interaction with the DB. It's pretty foundational to do the heavy lifting at the DB if you ever want to do any multi user networked based application anyway. DB business rules are especially useful in multi player Game Programming, even more useful in multiple platform game programming.
Really great article with very interesting information. You might want to follow up to this topic!?! 2011
Really great article with very interesting information. You might want to follow up to this topic!?! 2011
THIS IS SUPER FAST:
SELECT ID FROM TABLE
TABLESAMPLE (1 PERCENT)
I just want to say what a great blog you got ! I've been around quite a while, but finally decided to show my appreciation for your work ! Thumbs up, and keep it !
thank you sir....really awesome and helpful queries.
$person=mysql_fetch_row(mysql_query("SELECT username FROM users WHERE online > '$online' ORDER BY RAND() LIMIT 1"));

how would i select 10 random users
select top 1 * from Tablename order by newid()

select top 10 * from Tablename order by newid()
can anyone tell me how to get the 2nd row from each group in postgresql
i need how to delete data from gridview and also database using c # coding

pls help me any one
i want to display only first 50 character in news headline from chunk of words from database..so plz help me
Yes ... the design is clearly needed to be changed :)
The dark green color would fit perfectly xD
This helps a lot.
thank you!
buy [URL=http://blog.sman1baturetno.sch.id/designerfakehandbags/ - fake designer purses[/URL - and check coupon code available FgwlhgGK [URL - http://designerfake.komunitasonline.com/[/URL -
i want to know that how can prevent data come again in random mode ? in random mode data show again and again. thanks
We were given and assignment to select only two records from the list of agents..i.e 2 random records for each agent over the span of a week etc.... and below is what we got and it works

with summary as ( Select Dbms_Random.Random As Ran_Number, colmn1, colm2, colm3 Row_Number() Over(Partition By col2 Order By Dbms_Random.Random) As Rank From table1, table2 Where Table1.Id = Table2.Id Order By Dbms_Random.Random Asc) Select tab1.col2, tab1.col4, tab1.col5, From Summary s Where s.Rank <= 2;
Here, i want select row randomly then after selecting row i want to selct attribute randomly in sql can you help me??

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?