Random sample from MySQL Database

[Rahul Anand]

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

[Tom Thackrey]

Re: Random sample from MySQL Database


On 25-Dec-2003, rahulanand_bis@ rediffmail.com (Rahul Anand) wrote:
[color=blue]
> 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.[/color]

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

--
Tom Thackrey

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)

[Shawn Wilson]

Re: Random sample from MySQL Database

Rahul Anand wrote:[color=blue]
>
> 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?[/color]

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
shawn@glassgian t.com

http://www.glassgiant.com

[Rahul Anand]

Re: Random sample from MySQL Database

Shawn Wilson <shawn@glassgia nt.com> wrote in message news:<3FEC3B7D. 34F06143@glassg iant.com>...[color=blue]
> Rahul Anand wrote:[color=green]
> >
> > 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?[/color]
>
> 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[/color]


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

[Tom Thackrey]

Re: Random sample from MySQL Database


On 26-Dec-2003, rahulanand_bis@ rediffmail.com (Rahul Anand) wrote:
[color=blue]
> "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[/color]

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

--
Tom Thackrey

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)