469,336 Members | 5,442 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,336 developers. It's quick & easy.

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 13322

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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Bart Nessux | last post: by
reply views Thread by Jason McCormick | last post: by
reply views Thread by Tony Stephens | last post: by
1 post views Thread by steflhermitte | last post: by
1 post views Thread by jaYPee | last post: by
12 posts views Thread by Jim Michaels | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.