By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,502 Members | 1,159 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,502 IT Pros & Developers. It's quick & easy.

Random sample from MySQL Database

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a

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

P: n/a
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

P: n/a
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

P: n/a

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.