SQL to Select a random row from a database table

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

Trackback Address: 466/2A738BB0EE08AFD98813801EAD41CB70

Comments

On 09/14/2005 at 1:32:44 PM UTC Bryan F. Hogan wrote:
1
Fortunately additional application logic allows your app to be cross DB.

On 09/14/2005 at 1:36:07 PM UTC Pete Freitag wrote:
2
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.

On 09/14/2005 at 1:38:03 PM UTC Bryan F. Hogan wrote:
3
Agree, just adding more information to your post.

On 09/14/2005 at 1:39:38 PM UTC Pete Freitag wrote:
4
Thanks, I probably should have posted a bit about that tradeoff in the post - oh well its down here now.

On 09/14/2005 at 3:42:29 PM UTC Mark Murphy wrote:
5
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

On 09/14/2005 at 4:55:45 PM UTC Pete Freitag wrote:
6
Thanks Mark - updated.

On 09/15/2005 at 12:39:23 PM UTC Barney wrote:
7
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.

On 09/23/2005 at 8:33:13 AM UTC Giles Morant wrote:
8
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.

On 10/17/2005 at 7:25:42 AM UTC Casper wrote:
9
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 :-)

On 11/14/2005 at 8:56:23 AM UTC Elango wrote:
10
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

On 11/21/2005 at 7:09:37 PM UTC Shupei Wang wrote:
11
Thanks. The systax worked perfect on my Oracle 9.2, and I used it in one of my SQR reports.

On 11/28/2005 at 4:05:20 PM UTC Latha wrote:
12
Hi guys,Thanx , it worked perfectly on my oracle database n retrived the rows i reqd for an update

On 12/02/2005 at 9:17:53 AM UTC pp wrote:
13
How to delete the duplicate values (if 2 rows there, delete one row)in single table

On 12/22/2005 at 2:45:31 AM UTC ankush wrote:
14
how to retrieve second record from a table without using the where clause

On 12/22/2005 at 8:53:51 AM UTC Pete Freitag wrote:
15
To get the second record on mysql or postgresql you can use LIMIT and OFFSET, eg:

SELECT col FROM tbl LIMIT 1 OFFSET 1

On 01/10/2006 at 3:30:04 AM UTC Shivprakash wrote:
16
It's a very helpful media which solves our difficult problem. Really It helps me more!!!!!1

On 01/18/2006 at 10:13:58 AM UTC Steve wrote:
17
It's great info - but I can't get it working in my script using a MDB Database via ODBC

On 01/21/2006 at 7:31:27 AM UTC pabloj wrote:
18
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()

On 02/03/2006 at 11:29:05 PM UTC hemant wrote:
19
how can pull two records from mysql randomly

On 02/05/2006 at 4:21:32 AM UTC Ali wrote:
20
what about MsAccess databse, how to select random row?

On 02/10/2006 at 11:44:32 AM UTC Greg wrote:
21
hemant: just adjust the LIMIT clause: SELECT column FROM table ORDER BY RAND() LIMIT 2 will pull two rows.

On 03/11/2006 at 1:24:43 AM UTC John wrote:
22
How can I select a random record from a random table? Can anyone help?

On 03/22/2006 at 1:55:40 AM UTC Alex S wrote:
23
Great article, very useful, it helped me avoid using stored proceedures and cursors for a specific solution

On 03/29/2006 at 4:35:04 PM UTC Tony Nguyen wrote:
24
Is there any one know SQL Random function for NCR Teradata? thanks so much for reading this.

On 04/12/2006 at 5:32:15 PM UTC Anonymous wrote:
25
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)

On 05/06/2006 at 8:52:24 AM UTC Renugopal D wrote:
26
In Teradata,we can get random records use the below given Query.

Select cols From table SAMPLE RANDOMIZED ALLOCATION 10

Thanks Renu

On 07/05/2006 at 9:39:16 AM UTC Anonymous wrote:
27
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

On 07/12/2006 at 8:26:46 AM UTC Damien Conlon wrote:
28
Oracle also has a SAMPLE function, similar to Teradata mentioned above.

On 07/12/2006 at 9:28:04 AM UTC sfsdfsdfsdf wrote:
29
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 :-)

On 07/23/2006 at 10:44:36 AM UTC siim wrote:
30
For PostgreSQL this worked much better:

SELECT * FROM table OFFSET RANDOM() LIMIT 1;

On 08/03/2006 at 6:38:30 AM UTC Ashutosh Dixit wrote:
31
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

On 08/09/2006 at 10:20:17 PM UTC Jawad.H wrote:
32
I want to delete first 10 rows in sql 2000, can any one help me?

On 09/04/2006 at 6:54:48 AM UTC Farrukh Shahzad wrote:
33
>>I want to delete first 10 rows in sql 2000, can any one help me?

Delete Top 10 from [table_name]

On 09/04/2006 at 7:18:38 AM UTC Jens wrote:
34
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

On 09/14/2006 at 8:07:08 AM UTC Shantanu wrote:
35
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

On 10/06/2006 at 4:42:58 PM UTC Nathan Logan wrote:
36
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!

On 10/12/2006 at 11:29:04 AM UTC Hill wrote:
37
Can anyone help me in "How to delete first 10 rows in db2 udb"

Thank You Hill

On 10/27/2006 at 11:59:22 PM UTC rakesh wrote:
38
whts the sql statement to select one record at a time.the next record want to see when we click next button.

On 10/31/2006 at 10:58:23 PM UTC Niraan wrote:
39
Its greate.

On 11/06/2006 at 2:12:16 AM UTC Saravanan wrote:
40
Can anydody tell me how to delete all the duplicate rows in a table. The database in POSTGRESQL. Thanks in advance

On 11/13/2006 at 3:10:02 AM UTC Shailendra Soni wrote:
41
How to select data in second row from a table in SQL Server 2000?

On 11/13/2006 at 3:11:46 AM UTC Shailendra Soni wrote:
42
How to select data in second row from a table in SQL Server 2000?

On 11/15/2006 at 1:07:57 AM UTC AMIT KUMAR wrote:
43
How to delete one of the two duplicate rows

On 11/15/2006 at 1:09:43 AM UTC AMIT KUMAR wrote:
44
How to create procedure using ( two tables from one database and two tables from another database) in ms-sql

On 11/21/2006 at 11:07:38 PM UTC senthilkumar wrote:
45
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.

On 11/22/2006 at 12:23:00 AM UTC Ramya wrote:
46
How to change the order of the columns in the created table?

On 11/23/2006 at 1:04:08 AM UTC Ashutosh Mani Tiwari wrote:
47
How I can delete any random row from a table.

On 11/27/2006 at 6:24:07 AM UTC Aruna Krishnan wrote:
48
How to select top 10 records?

On 12/11/2006 at 3:55:31 PM UTC Samir wrote:
49
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?

On 12/12/2006 at 6:56:45 AM UTC Observer wrote:
50
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! ;-)

On 12/18/2006 at 2:56:15 AM UTC Rohini wrote:
51
how to get random values in SQL.

On 12/18/2006 at 7:37:21 PM UTC Dan Kaufman wrote:
52
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.

On 01/03/2007 at 11:35:11 PM UTC umesh wrote:
53
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

On 01/04/2007 at 7:53:51 AM UTC James wrote:
54
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.

On 01/08/2007 at 3:05:56 AM UTC Nonnamiss wrote:
55
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!

On 01/19/2007 at 12:44:55 AM UTC Ravi wrote:
56
Hi,

This is Ravi, need to know how to fetch the random rows in sybase.

Thanks

Ravi

On 01/19/2007 at 5:27:35 AM UTC firdaus wrote:
57
hello guys what up! please help me to learn MySQL syntax at PHP

On 01/23/2007 at 11:15:29 AM UTC Jeff S. wrote:
58
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.

On 01/25/2007 at 4:08:00 AM UTC balaramesh.t wrote:
59
how to retrive nth row from a table in sqlserver with out using top and cursor

On 01/25/2007 at 1:01:05 PM UTC sharu wrote:
60
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.

On 01/29/2007 at 2:27:14 AM UTC Giles Morant wrote:
61
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.

On 03/21/2007 at 2:15:47 AM UTC Bharat wrote:
62
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.

On 03/23/2007 at 4:16:22 AM UTC Ravi wrote:
63
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

On 04/07/2007 at 10:15:01 AM UTC someone wrote:
64
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.

On 04/19/2007 at 1:38:30 AM UTC Krish wrote:
65
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.

On 05/17/2007 at 7:56:30 PM UTC Anonymous wrote:
66
<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>

On 05/18/2007 at 11:59:04 PM UTC Rahul wrote:
67
I want to select some random records from MS Access Database. How to select it. Any help would be greatly appreciated. Thank You.

On 06/15/2007 at 2:29:08 PM UTC T Cox wrote:
68
Thanks for the SQL info on RAND() Functions. Much Appreciated.

On 07/05/2007 at 9:03:42 AM UTC SELVIN wrote:
69
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

On 07/17/2007 at 5:53:53 AM UTC Anonymous wrote:
70
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.

On 07/24/2007 at 5:07:05 PM UTC zack wrote:
71
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.

On 07/30/2007 at 9:22:53 AM UTC Timothy C. Fanelli wrote:
72
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

On 07/31/2007 at 10:58:39 AM UTC Pete Freitag wrote:
73
Thanks Tim! I've updated the example in the entry.

On 08/09/2007 at 10:50:32 AM UTC chris wrote:
74
That's great! Using SQL Server 2005, how would you select random rows via a function?

On 08/21/2007 at 7:17:54 AM UTC Kumar wrote:
75
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

On 08/23/2007 at 1:25:50 PM UTC Jake wrote:
76
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!

On 08/24/2007 at 7:34:25 AM UTC TravisO wrote:
77
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

On 08/29/2007 at 2:17:33 AM UTC Juddy wrote:
78
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

On 09/04/2007 at 6:48:58 AM UTC Ady wrote:
79
Thank you. TSQL query save me a lot of work.

On 09/25/2007 at 11:35:49 PM UTC Priya wrote:
80
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

On 09/27/2007 at 7:03:38 AM UTC pavan garg wrote:
81
How To see in SQL server six th row

On 10/19/2007 at 1:32:23 AM UTC chris wrote:
82
Thanks tim, and mark murphy !! you have done a great job, making things a lot easier for innumberable sql guys.

On 10/29/2007 at 3:32:01 AM UTC harish wrote:
83
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

On 11/09/2007 at 6:38:48 AM UTC Matheus Garcia wrote:
84
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

On 11/21/2007 at 8:39:07 PM UTC vmrejas wrote:
85
Hey thanks for that info

On 11/22/2007 at 1:24:31 PM UTC Tiago Braga wrote:
86
Thanks a log... very good post.

On 12/01/2007 at 5:40:04 PM UTC Jimmy wrote:
87
thanks for the great info. do you know how to select multiple random rows? not just a row. thanks

On 12/18/2007 at 12:11:32 AM UTC sandeep wrote:
88
i want to only 5th row in sql 2005 give me a query

On 12/31/2007 at 2:45:06 AM UTC benjamin blay wrote:
89
does the (Limit) statement in mysql work in Microsoft sql server 2000

On 01/11/2008 at 7:12:27 AM UTC Brian wrote:
90
MS Access: select top 1 * from tablename order by rnd(id)

id should be an autonumber field

On 01/14/2008 at 7:36:04 AM UTC Michael wrote:
91
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.

On 01/18/2008 at 5:21:21 AM UTC Evan wrote:
92
how would i go about randomly picking maybe 10 rows and having it save in another table?!?!?!?!

On 01/24/2008 at 3:51:06 AM UTC Ram wrote:
93
Hi How to fetch the 4th record alone from the table using sql?

On 01/31/2008 at 3:02:41 AM UTC Biswa Bhusan Dash wrote:
94
i have 4 tables, how i retrive data randomly from 4 tables in sql server 2000, please help thanx in advance

On 02/01/2008 at 5:18:36 AM UTC Prakher wrote:
95
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

On 02/05/2008 at 7:20:12 PM UTC Nejal wrote:
96
Very useful tip!

On 02/22/2008 at 12:29:20 AM UTC neha wrote:
97
How to get fifth highest record from table using top command in sql.

On 02/22/2008 at 12:30:09 AM UTC neha wrote:
98
How to get fifth highest record from table using top command in sql.

On 02/26/2008 at 6:07:02 AM UTC Raj wrote:
99
Hi this is very useful for me...thanks for posting this query.

On 03/05/2008 at 12:20:44 AM UTC laura wrote:
100
Hi, I'm a housewife. How do I select rows 18-32 from a random selected table ordered by color?

On 03/05/2008 at 4:38:59 AM UTC vineeth wrote:
101
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

On 03/18/2008 at 8:38:32 AM UTC vigneshwara wrote:
102
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

On 03/19/2008 at 10:48:34 PM UTC reddy wrote:
103
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 ?

On 03/23/2008 at 9:13:40 PM UTC SURAJIT DEY wrote:
104
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

On 03/28/2008 at 5:13:48 AM UTC mathavan wrote:
105
How to interchange to row to column to SQL database

On 03/31/2008 at 12:27:51 AM UTC alice wrote:
106
how to apply this to a jump menu?

On 04/05/2008 at 12:04:55 PM UTC vaibhav wrote:
107
i want to delete 1st row from table in ms sql server 2000 but there is primary key so plz help me

On 04/06/2008 at 11:04:18 AM UTC vishnodthehousewofe wrote:
108
i want to be able to google fors answer? how to do? tel me nows!

On 04/15/2008 at 4:12:30 PM UTC Khirod wrote:
109
Please post me a query that would swap the contents of two columns with same datatype, of course.

On 04/18/2008 at 9:59:05 PM UTC kartik shah wrote:
110
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

On 05/25/2008 at 4:24:01 AM UTC Alauddin wrote:
111
Hello, I am using postgresql database. I want to select rownum as serial no. How i solve it

Thank you in advance

On 06/30/2008 at 9:37:02 PM UTC vishnu vardhan wrote:
112
hai iam vishnu,send a query for below i mentioned.. select second highest salary in a employee table

On 06/30/2008 at 9:42:25 PM UTC vishnu vardhan wrote:
113
hai iam vishnu,send a query for below i mentioned.. select second highest salary rows in a employee table

On 07/22/2008 at 11:51:11 AM UTC Sam wrote:
114
Hotness thanks, this is exactly what I was looking for!

On 07/23/2008 at 12:53:14 AM UTC Jibanananda wrote:
115
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.

On 08/01/2008 at 4:00:57 PM UTC asdf wrote:
116
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

On 08/05/2008 at 7:14:06 PM UTC selken wrote:
117
I use this one in Oracle:

select * from (select rownum rowix, i.* from my_table order by dbms_random.random) where rowix < 2

On 08/20/2008 at 2:15:57 PM UTC Raiku wrote:
118
Thank you Pete!

On 09/03/2008 at 5:08:48 AM UTC shivakumar wrote:
119
plz tell me the sql query for the record >2and=<6 ,the identical records select in a single row .

On 09/11/2008 at 10:59:50 PM UTC shivakumar wrote:
120
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

On 09/16/2008 at 3:59:10 AM UTC Anonymous wrote:
121
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.

On 09/25/2008 at 8:18:44 AM UTC raj wrote:
122
I use the random row in sql server code but it not work properly.

On 09/28/2008 at 11:57:07 PM UTC paul wrote:
123
How do I write sql statement regardless of the DB platform (DB Independence) to select only 1 record?

On 09/29/2008 at 4:33:21 PM UTC HASSAN wrote:
124
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

On 10/11/2008 at 2:27:06 AM UTC Kavitha wrote:
125
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

On 10/11/2008 at 2:28:12 AM UTC Kavitha wrote:
126
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

On 10/13/2008 at 10:50:49 PM UTC John wrote:
127
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.

On 10/16/2008 at 11:25:11 PM UTC john wrote:
128
how to select the top 5 rows from the tables in sybase

On 10/22/2008 at 9:21:56 AM UTC Anonymous wrote:
129
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

On 10/29/2008 at 5:48:12 PM UTC versa wrote:
130
to Kavitha select Marks, name from studenttable order by Marks asc try this one

On 11/03/2008 at 4:42:58 AM UTC dsf wrote:
131
dsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsf dsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsf vdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsfdsf

On 11/12/2008 at 4:54:12 AM UTC justin wrote:
132
how can i generate random number ins MSSQL that are unique? i rily nid help on this one..

On 11/13/2008 at 9:33:17 PM UTC ilango wrote:
133
How to select a column value in single row

On 11/23/2008 at 2:43:29 PM UTC asilumSt wrote:
134
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..

On 11/23/2008 at 10:56:44 PM UTC Haque wrote:
135
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

On 11/24/2008 at 9:36:05 PM UTC naveen wrote:
136
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...

On 12/02/2008 at 3:16:28 AM UTC rajesh reddy wrote:
137
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

On 02/17/2009 at 11:32:44 AM UTC Eric Goff wrote:
138
Although elegant, it still requires a scan of all rows.

On 02/23/2009 at 6:49:39 AM UTC Sander Valcke wrote:
139
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

On 02/24/2009 at 7:16:53 AM UTC anilkumar wrote:
140
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]" )

On 03/12/2009 at 9:37:33 AM UTC Mario Roberts wrote:
141
Thanks for the sql code it works great!

On 04/07/2009 at 4:30:18 AM UTC Anonymous wrote:
142
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.

On 04/07/2009 at 4:37:07 AM UTC Khan wrote:
143
Solution for Random in sql server 2005 is,, select TOP 1 first_name from employee order by newid()

On 04/07/2009 at 4:37:26 AM UTC Khan wrote:
144
Solution for Random in sql server 2005 is,, select TOP 1 first_name from employee order by newid()

On 04/07/2009 at 4:38:23 AM UTC Khan wrote:
145
Solution for Random in sql server 2005 is,, select TOP 1 first_name from employee order by newid()

On 04/16/2009 at 5:02:36 AM UTC thanika wrote:
146
how to select more than one value in single row, the coloum names are like feature1, feature2, feature3,feature4....etc

On 04/16/2009 at 10:43:27 AM UTC sudhir wrote:
147
how to include table as a record int the table

On 04/30/2009 at 9:24:03 PM UTC aa wrote:
148
aat more than one value in single row, the coloum names are like featu

On 05/08/2009 at 1:01:04 AM UTC Praveen Dani wrote:
149
I want to Select Recordsets randomly from a DB with where conditions . . . Please Give solution for me . . .

On 05/28/2009 at 8:21:23 AM UTC OKECHUKWU wrote:
150
how can i select or delect a particular row from a table in my database using sql*plus

On 06/09/2009 at 3:05:58 PM UTC T Downs wrote:
151
Thank you- Very helpful. Exactly what I needed for SQL Server.

On 06/25/2009 at 2:56:57 AM UTC MATTHIJS wrote:
152
Does anyone know how to "remember" a random selection so i can preform a second action on the same records? Using MS-SQLserver 2000

On 06/25/2009 at 4:01:59 AM UTC kavitha wrote:
153
how to delete top n records in terdata?

On 07/02/2009 at 7:17:18 AM UTC Anil wrote:
154
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.

On 07/09/2009 at 1:55:47 AM UTC binoj wrote:
155
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.

On 07/20/2009 at 6:00:19 AM UTC hankhill wrote:
156
great sql, thanks for convering all types - helped me alot

On 08/07/2009 at 5:10:01 AM UTC Boney wrote:
157
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.

On 08/24/2009 at 3:35:31 PM UTC Mike wrote:
158
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/

On 08/31/2009 at 8:35:47 AM UTC mohan wrote:
159
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.

On 08/31/2009 at 11:59:11 AM UTC photatca wrote:
160
Could anyone please let me know how to select random record from Informix 7? Thanks a lot.

On 08/31/2009 at 5:29:33 PM UTC Ozzie wrote:
161
Thank you, Pete.

For your help.

PD: Your little article help to management some data in Chile, South America

On 09/29/2009 at 7:43:03 AM UTC Pallav Vidwans wrote:
162
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

On 10/05/2009 at 4:01:04 PM UTC Frank wrote:
163
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.

On 10/14/2009 at 12:12:16 AM UTC Amirth wrote:
164
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 ....

On 10/23/2009 at 12:48:14 PM UTC Soumen Ghosh wrote:
165
How i can insert a column in a table from random selecting another table?

On 11/08/2009 at 6:13:34 PM UTC Roque wrote:
166
Somebody knows how can I do that in sqlitle?? on android

On 11/09/2009 at 6:33:48 AM UTC Anish wrote:
167
Access related SQL query tested with all suggested strings. But getting not a random recordset ! Any help ?

On 11/13/2009 at 5:00:30 AM UTC Emin Cavadov wrote:
168
SELECT * FROM table_name WHERE field IN (

SELECT field FROM table ORDER BY RAND( ) ) ORDER BY RAND( ) LIMIT 15 , 60

On 11/20/2009 at 9:45:46 PM UTC rey wrote:
169
How if there condition? like select coloum from table where coloum = 'id' order by rand();

On 11/24/2009 at 1:10:06 PM UTC Piyush wrote:
170
Hi Murphy,

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

Thanks once again, Piyush

On 11/30/2009 at 6:51:00 AM UTC web wrote:
171
How do i select at random from mysql and have he result arranged alphabetically?

On 11/30/2009 at 3:17:28 PM UTC Ilan Hazan wrote:
172
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/

On 12/04/2009 at 7:57:31 AM UTC Nebu wrote:
173
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

On 12/14/2009 at 4:18:36 AM UTC prasad wrote:
174
hi. hoe to get the rows no 5 to 10, in sql server.

On 12/16/2009 at 5:52:56 AM UTC boyiniramana wrote:
175
to get the rows no 5 to 10, in sql server

using between operator

On 02/03/2010 at 1:08:19 AM UTC satish wrote:
176
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+ --------

On 03/07/2010 at 12:13:05 PM UTC Gurudatta wrote:
177
Select all employee names from emp table but display records of employees in Upper Case who get sal>3000. HOW?

On 03/10/2010 at 3:29:36 AM UTC Hafsal wrote:
178
Thanks a lot!!!

On 03/15/2010 at 3:11:20 AM UTC pripa wrote:
179
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?

On 03/15/2010 at 3:50:15 PM UTC sivakumar wrote:
180
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

On 03/16/2010 at 2:20:34 AM UTC Nizam wrote:
181
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!

On 03/19/2010 at 5:08:59 AM UTC Anonymous wrote:
182
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?

On 03/19/2010 at 9:06:08 AM UTC Anonymous wrote:
183
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

On 04/14/2010 at 3:14:31 AM UTC juan wrote:
184
@anonymous try fulltextsearch in sql

On 04/22/2010 at 1:28:57 AM UTC vinod wrote:
185
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

On 04/23/2010 at 6:28:36 AM UTC er wrote:
186
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

On 04/28/2010 at 4:58:29 AM UTC Shreyas wrote:
187
thanks i use this query which help me alot and reduce my work

On 05/03/2010 at 5:07:49 AM UTC bdwankhede wrote:
188
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..

On 05/04/2010 at 4:10:06 AM UTC Programmer wrote:
189
How to Select a Row in Microsoft SQL Server Management Studio Express??? Thx in Advance

On 05/04/2010 at 5:17:47 AM UTC Manifesto wrote:
190
it works but i want only to select 1 row... how can i do that?

On 05/10/2010 at 4:13:35 PM UTC carol wrote:
191
*PLEASE* add 1 of the most popular public-domain databases on the planet today: Sqlite

On 05/14/2010 at 6:58:45 AM UTC Ramanand wrote:
192
i want to delete distinct row like

stuno name 101 mandar 101 ram so one 101 should be delete pl reply

On 05/15/2010 at 5:17:17 AM UTC mack wrote:
193
I also want to delete distinct row

please help me.ww.adnpost.com

On 05/17/2010 at 8:17:23 AM UTC Amit wrote:
194
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

On 05/17/2010 at 8:18:28 AM UTC Amit wrote:
195
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

On 05/17/2010 at 9:01:35 AM UTC Anonymous wrote:
196
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)

On 05/25/2010 at 6:05:43 PM UTC Phillip Senn wrote:
197
"ORDER BY NEWID()" brilliant!

On 06/01/2010 at 5:36:06 AM UTC Sooraj wrote:
198
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..

On 06/09/2010 at 8:18:20 AM UTC srinivas wrote:
199
How to select a nth from a table

On 06/09/2010 at 1:11:11 PM UTC xhtml wrote:
200
ORDER BY NEWID() Perfect solution!!!

On 07/03/2010 at 10:35:41 PM UTC SalmanAbbas007 wrote:
201
Perfect Bro You saved my Time

On 07/22/2010 at 11:52:15 AM UTC paty wrote:
202
Excellent solution!! I can resolve my problem in Oracle..

Thanks from Argentina...

On 07/27/2010 at 4:44:50 AM UTC Soumya Basu wrote:
203
Using of RAND() is awesome but can I select a random table from a mysql database without using RAND()? plz help me...

On 07/29/2010 at 4:46:29 PM UTC nitin chaudhary wrote:
204
will you tell me about sql query to get the random data from the ms access 07 database

On 07/29/2010 at 4:46:54 PM UTC nitin chaudhary wrote:
205
will you tell me about sql query to get the random data from the ms access 07 database

On 08/09/2010 at 4:36:34 PM UTC Wes wrote:
206
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?

On 08/09/2010 at 4:36:43 PM UTC Wes wrote:
207
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?

On 10/04/2010 at 4:08:54 PM UTC insurance intermediary qualification wrote:
208
Hey Bernice, WTF?!

On 10/23/2010 at 10:18:14 AM UTC JiAngelo wrote:
209
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.

On 11/11/2010 at 11:51:00 AM UTC Azeem Michael wrote:
210
Thanks, saved my time -- needed to delete rand 200 recs. Worked without a problem.

On 11/11/2010 at 11:51:11 AM UTC Azeem Michael wrote:
211
Thanks, saved my time -- needed to delete rand 200 recs. Worked without a problem.

On 11/24/2010 at 10:01:32 AM UTC Anonymous wrote:
212
Hi,How to find 3rd minimum salary from salary column in SQL

On 11/24/2010 at 10:01:59 AM UTC Pakyaraj wrote:
213
Hi,How to find 3rd minimum salary from salary column in SQL

On 11/24/2010 at 10:02:07 AM UTC Pakyaraj wrote:
214
Hi,How to find 3rd minimum salary from salary column in SQL

On 11/24/2010 at 10:02:30 AM UTC Pakyaraj wrote:
215
Hi,How to find 3rd minimum salary from salary column in SQL

On 11/27/2010 at 7:08:19 AM UTC alaa wrote:
216
query sql to retrieve image from table in oracle

On 11/29/2010 at 5:55:58 AM UTC Ismaeel Ameen wrote:
217
In case anyone is interested in Ingres you do it like this

SELECT top 1 column FROM table ORDER BY RANDOM()

On 11/29/2010 at 5:56:28 AM UTC Ismaeel Ameen wrote:
218
In case anyone is interested in Ingres you do it like this

SELECT top 1 column FROM table ORDER BY RANDOM()

On 11/29/2010 at 5:56:39 AM UTC Ismaeel Ameen wrote:
219
In case anyone is interested in Ingres you do it like this

SELECT top 1 column FROM table ORDER BY RANDOM()

On 11/29/2010 at 5:56:50 AM UTC Ameen wrote:
220
In case anyone is interested in Ingres you do it like this

SELECT top 1 column FROM table ORDER BY RANDOM()

On 12/08/2010 at 9:16:18 AM UTC Kerplunk wrote:
221
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.

On 12/08/2010 at 9:16:42 AM UTC Kerplunk11 wrote:
222
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.

On 12/22/2010 at 7:37:04 AM UTC Krishna wrote:
223
Thanks its very helpfull

On 12/22/2010 at 7:37:08 AM UTC Krishna wrote:
224
Thanks its very helpfull

On 12/29/2010 at 4:30:37 AM UTC jsimlo wrote:
225
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.

On 12/29/2010 at 4:30:58 AM UTC jsimlo wrote:
226
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.

On 12/29/2010 at 4:31:50 AM UTC jsimlo wrote:
227
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.

On 01/27/2011 at 7:47:04 AM UTC John Haugeland wrote:
228
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.

On 02/14/2011 at 12:02:35 AM UTC Manish Choudhary wrote:
229
Similar results can be found on link http://cybosofttech.com/select-random-record-by-sql/ with example.

On 02/14/2011 at 12:02:43 AM UTC Manish Choudhary wrote:
230
Similar results can be found on link http://cybosofttech.com/select-random-record-by-sql/ with example.

On 02/14/2011 at 12:02:50 AM UTC Manish Choudhary wrote:
231
Similar results can be found on link cybosofttech.com/select-random-record-by-sql/ with example.

On 02/14/2011 at 12:03:28 AM UTC Manish Choudhary wrote:
232
Similar results can be found on link cybosofttech.com/select-random-record-by-sql/ with example.

On 02/16/2011 at 5:18:14 PM UTC WillSmith wrote:
233
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

On 02/28/2011 at 8:26:16 AM UTC bsn nitrix моÑ?ква wrote:
234
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

On 03/29/2011 at 5:18:23 AM UTC sravanthi wrote:
235
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

On 03/29/2011 at 5:19:05 AM UTC sravanthi wrote:
236
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

On 03/29/2011 at 5:19:45 AM UTC sravanthi wrote:
237
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

On 03/29/2011 at 5:20:31 AM UTC sravanthi wrote:
238
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

On 03/29/2011 at 5:20:50 AM UTC sravanthi wrote:
239
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

On 05/03/2011 at 12:58:06 AM UTC zaheer wrote:
240
IF RANDOM SINGLE ROW DISPLAY IS DESIRED. SQL2K5

SELECT TOP 1 * FROM EMPLOYEES ORDER BY NEWID();

On 05/03/2011 at 12:58:58 AM UTC zaheer wrote:
241
IF RANDOM SINGLE ROW DISPLAY IS DESIRED. SQL2K5

SELECT TOP 1 * FROM EMPLOYEES ORDER BY NEWID();

On 05/03/2011 at 12:25:44 PM UTC Andrew wrote:
242
thanks for this code, it helped me a lot!

On 05/07/2011 at 1:46:59 PM UTC pcpisipc wrote:
243
thanks a lot.

On 06/09/2011 at 4:12:56 AM UTC damian wrote:
244
sqlite :

SELECT column FROM table ORDER BY RANDOM() LIMIT 1

On 06/13/2011 at 1:51:31 PM UTC Andu wrote:
245
Thanks for this.

On 06/23/2011 at 3:28:42 AM UTC sam wrote:
246
thank you!

On 06/30/2011 at 4:13:25 AM UTC Anonymous wrote:
247
thax for hlpng me

On 07/02/2011 at 2:02:09 AM UTC Anonymous wrote:
248
How to compare corresponding rows in a table????

On 07/06/2011 at 8:25:23 AM UTC chandru wrote:
249
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()

On 07/07/2011 at 12:11:39 PM UTC CG wrote:
250
Awesome functions, thanks!

On 08/13/2011 at 4:38:55 PM UTC pristiq reviews wrote:
251
Great article with very interesting information. You might want to follow up to this topic!?!

On 08/13/2011 at 11:22:20 PM UTC pristiq reviews wrote:
252
Great article with very interesting information. You might want to follow up to this topic!?!

On 08/18/2011 at 11:20:56 PM UTC Kevin wrote:
253
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.

On 08/20/2011 at 7:04:40 PM UTC one good guy wrote:
254
Really great article with very interesting information. You might want to follow up to this topic!?! 2011

On 08/22/2011 at 10:43:02 AM UTC lexapro online wrote:
255
Really great article with very interesting information. You might want to follow up to this topic!?! 2011

On 08/23/2011 at 11:36:24 AM UTC Anonymous wrote:
256
THIS IS SUPER FAST: SELECT ID FROM TABLE TABLESAMPLE (1 PERCENT)

On 09/05/2011 at 1:13:49 PM UTC ?????? ???????????????? wrote:
257
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 !

On 09/24/2011 at 4:31:11 AM UTC robin singh wrote:
258
thank you sir....really awesome and helpful queries.

On 10/02/2011 at 6:35:36 PM UTC danny wrote:
259
$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

On 10/19/2011 at 8:51:51 AM UTC osama safwat wrote:
260
select top 1 * from Tablename order by newid()

select top 10 * from Tablename order by newid()

On 12/05/2011 at 2:02:03 AM UTC Anonymous wrote:
261
can anyone tell me how to get the 2nd row from each group in postgresql

On 12/05/2011 at 7:58:11 AM UTC Rajeswari wrote:
262
i need how to delete data from gridview and also database using c # coding

pls help me any one

On 01/18/2012 at 5:13:52 AM UTC Viku wrote:
263
i want to display only first 50 character in news headline from chunk of words from database..so plz help me

On 05/17/2012 at 7:15:14 PM UTC Menelok wrote:
264
Yes ... the design is clearly needed to be changed :) The dark green color would fit perfectly xD

On 06/07/2012 at 10:21:20 PM UTC JrDBA wrote:
265
This helps a lot.

On 06/22/2012 at 3:41:02 PM UTC jay wrote:
266
thank you!

On 10/20/2012 at 9:53:27 PM UTC italmemI wrote:
267
buy [URL=http://blog.sman1baturetno.sch.id/designerfakehandbags/ - fake designer purses[/URL - and check coupon code available FgwlhgGK [URL - http://designerfake.komunitasonline.com/[/URL -

On 12/11/2012 at 5:58:29 AM UTC hiren wrote:
268
i want to know that how can prevent data come again in random mode ? in random mode data show again and again. thanks

On 05/01/2013 at 3:59:19 PM UTC ChrisNZak wrote:
269
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;

On 12/06/2013 at 3:00:47 AM UTC richa wrote:
270
Here, i want select row randomly then after selecting row i want to selct attribute randomly in sql can you help me??

On 08/22/2014 at 8:47:29 AM UTC abercrombie wrote:
271
SQL to Select a random row from a database table abercrombie http://www.abercrombiecanada.ca SQL to Select a random row from a database table

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?