473,239 Members | 1,795 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,239 software developers and data experts.

Random sample from MySQL Database

Hi all,

I am trying to fetch 5 records, randomly picked and in random order
from a MySQL table (MySQL version > 3.23).

I wrote my SQL Query as

SELECT name,id FROM tablename WHERE active = 'Y' ORDER BY RAND() LIMIT
5

But its not working. It returns records in a fixed order every time.
Can anybody suggest why its not working the expected way?

I found an alternative solution and used the following query

SELECT name,id FROM tablename WHERE active = 'Y' ORDER BY MD5(RAND())
LIMIT 5

Its working fine :-)

But still i am not clear why the first query is failing? :-(

Thanks in advance.

-- Rahul
Jul 17 '05 #1
4 13552

On 25-Dec-2003, ra************@rediffmail.com (Rahul Anand) wrote:
I am trying to fetch 5 records, randomly picked and in random order
from a MySQL table (MySQL version > 3.23).

I wrote my SQL Query as

SELECT name,id FROM tablename WHERE active = 'Y' ORDER BY RAND() LIMIT
5

But its not working. It returns records in a fixed order every time.
Can anybody suggest why its not working the expected way?

I found an alternative solution and used the following query

SELECT name,id FROM tablename WHERE active = 'Y' ORDER BY MD5(RAND())
LIMIT 5

Its working fine :-)

But still i am not clear why the first query is failing? :-(

Thanks in advance.


I was unable to reproduce your problem. My query returned 5 random records
in random sequence each time..

--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)
Jul 17 '05 #2
Rahul Anand wrote:

Hi all,

I am trying to fetch 5 records, randomly picked and in random order
from a MySQL table (MySQL version > 3.23).

I wrote my SQL Query as

SELECT name,id FROM tablename WHERE active = 'Y' ORDER BY RAND() LIMIT
5

But its not working. It returns records in a fixed order every time.
Can anybody suggest why its not working the expected way?


From http://www.mysql.com/doc/en/Mathemat...unctions.html:

You can't use a column with RAND() values in an ORDER BY clause, because ORDER
BY would evaluate the column multiple times. From version 3.23 you can do:
SELECT * FROM table_name ORDER BY RAND() This is useful to get a random sample
of a set SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT
1000. Note that a RAND() in a WHERE clause will be re-evaluated every time the
WHERE is executed. RAND() is not meant to be a perfect random generator, but
instead a fast way to generate ad hoc random numbers that will be portable
between platforms for the same MySQL version.
So try:

SELECT * FROM tablename WHERE active = 'Y' ORDER BY RAND() LIMIT 5

and my guess is you'll get your result.

Regards,
Shawn
--
Shawn Wilson
sh***@glassgiant.com
http://www.glassgiant.com
Jul 17 '05 #3
Shawn Wilson <sh***@glassgiant.com> wrote in message news:<3F***************@glassgiant.com>...
Rahul Anand wrote:

Hi all,

I am trying to fetch 5 records, randomly picked and in random order
from a MySQL table (MySQL version > 3.23).

I wrote my SQL Query as

SELECT name,id FROM tablename WHERE active = 'Y' ORDER BY RAND() LIMIT
5

But its not working. It returns records in a fixed order every time.
Can anybody suggest why its not working the expected way?


From http://www.mysql.com/doc/en/Mathemat...unctions.html:

You can't use a column with RAND() values in an ORDER BY clause, because ORDER
BY would evaluate the column multiple times. From version 3.23 you can do:
SELECT * FROM table_name ORDER BY RAND() This is useful to get a random sample
of a set SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT
1000. Note that a RAND() in a WHERE clause will be re-evaluated every time the
WHERE is executed. RAND() is not meant to be a perfect random generator, but
instead a fast way to generate ad hoc random numbers that will be portable
between platforms for the same MySQL version.
So try:

SELECT * FROM tablename WHERE active = 'Y' ORDER BY RAND() LIMIT 5

and my guess is you'll get your result.

Regards,
Shawn

Hi Shawn,

I also have read the documentation about this:-

"You can't use a column with RAND() values in an ORDER BY clause,
because ORDER
BY would evaluate the column multiple times."

As i interpret:
It says you cant use a RAND() column in *select-field-list* as well as
in ORDER BY clause because with each call RAND() will return a
different number.

You can not use it in where clause either for the same reason.

My query does not use RAND() in select-field-list or where clause.

I checked my first query in MySQL ver 4.1 and its working fine.

As per documentation:

"From version 3.23 you can do:
SELECT * FROM table_name ORDER BY RAND()"

But i am unable to do this in my MySQL 3.23.54

--
regards,
Rahul
Jul 17 '05 #4

On 26-Dec-2003, ra************@rediffmail.com (Rahul Anand) wrote:
"From version 3.23 you can do:
SELECT * FROM table_name ORDER BY RAND()"

But i am unable to do this in my MySQL 3.23.54


It works for me on 3.23.55, I think you have some other problem.

--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)
Jul 17 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Bart Nessux | last post by:
New to Python... trying to figure out how to count the objects in a list and then map the count to the objects or convert the list to a dict... I think the latter would be better as I need a number...
9
by: Bart Nessux | last post by:
I am using method 'a' below to pick 25 names from a pool of 225. A co-worker is using method 'b' by running it 25 times and throwing out the winning name (names are associated with numbers) after...
0
by: Jason McCormick | last post by:
Hello all, I was wondering if someone could shed some light on this problem I'm having. I have 3 slave MySQL servers replication from a master machine. The master machine is running only MySQL...
0
by: Tony Stephens | last post by:
I have a database table that contains a set of numbers (set X). I need to be able to generate/export a random set of survey numbers (set C) from set A with IDs that aren't in set B. Where set A is...
1
by: steflhermitte | last post by:
Dear cpp-ians, I want to apply a stratified sampling on an image. Following simplified example will explain my problem. The original image I with nrows and ncols is now a vector V of length...
1
by: jaYPee | last post by:
I am planning to use mysql and vb.net to my new project. this is an enrollment system. I want to know where can i find sample of vb.net that connects to mysql database. thanks in advance
3
by: VB.NET | last post by:
I'm using a mysql database and connecting my vb.net program to the DB over a network connection. i would like to bring this data over to a vb.net random access file. does anyone know how to...
12
by: Jim Michaels | last post by:
I need to generate 2 random numbers in rapid sequence from either PHP or mysql. I have not been able to do either. I get the same number back several times from PHP's mt_rand() and from mysql's...
26
by: Jimmy | last post by:
ill have a database with 1 table and 3 fields: ID FIRSTNAME LASTNAME (the ID field will be the auto incrementing index) there might be 10 records in the DB, there might be 10,000. i...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.