Selecting random record problem

Hello,

I think that question was asked many times on the list but I searched
archives and found nothing suitable for me.

That's my problem: I need to select one (two, three...) random records from
a table.

Here's what I do:

SELECT * FROM banners ORDER BY RAND() LIMIT 1

It works for persistent connections BUT it returns the same record if the
connection is reestablished every time after running this query! (This takes
place is PHP scripts, for example.)

I also tried using sequence seed but that didn't help too:

SELECT * FROM banners ORDER BY RAND(NOW()) LIMIT 1

MySQL version is 3.23.

Any ideas?

Best regards, Denis Gerasimov
Outsourcing Services Manager,
VEKOS, Ltd.
www.vekos.ru



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Selecting random record problem

> That's my problem: I need to select one (two, three...) random records
from
> a table.
>
> Here's what I do:
>
> SELECT * FROM banners ORDER BY RAND() LIMIT 1
>
> It works for persistent connections BUT it returns the same record if the
> connection is reestablished every time after running this query! (This
takes
> place is PHP scripts, for example.)

Try this:

SELECT @num_of_rec:=COUNT(*) FROM banners;

SELECT @r:=TRUNCATE((@num_of_rec - 1) * RAND() + 1), banners.* FROM banners
ORDER BY 1 LIMIT 3;

This is "SQL commands only" solution :-)
Sometime you get the same result because "RAND() is not meant to be a
perfect random generator" (cite from MySQL manual).

Petr Vileta, Czech republic


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Selecting random record problem

Hello

> -----Original Message-----
> From: Petr Vileta [mailto:petr@practisoft.cz]
> Sent: Tuesday, December 21, 2004 4:55 PM
> To: MySQL Windows Users List
> Subject: Re: Selecting random record problem
>
> > That's my problem: I need to select one (two, three...) random records
> from
> > a table.
> >
> > Here's what I do:
> >
> > SELECT * FROM banners ORDER BY RAND() LIMIT 1
> >
> > It works for persistent connections BUT it returns the same record if
> the
> > connection is reestablished every time after running this query! (This
> takes
> > place is PHP scripts, for example.)
>
> Try this:
>
> SELECT @num_of_rec:=COUNT(*) FROM banners;
>
> SELECT @r:=TRUNCATE((@num_of_rec - 1) * RAND() + 1), banners.* FROM
> banners
> ORDER BY 1 LIMIT 3;

Why is it so complicated? Is there some more easy way to handle this?
e.g. pass some seed to RAND() etc.?

>
> This is "SQL commands only" solution :-)
> Sometime you get the same result because "RAND() is not meant to be a
> perfect random generator" (cite from MySQL manual).
>
> Petr Vileta, Czech republic
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
> http://lists.mysql.com/win32?unsub=denis.gerasimov@vekos.ru

Best regards, Denis Gerasimov
Outsourcing Services Manager,
VEKOS, Ltd.
www.vekos.ru


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org