Shawn Wilson <sh***@glassgia nt.com> wrote in message news:<3F******* ********@glassg iant.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
