At 3:19 PM -0800 12/8/01, Rob@TH wrote:
>Hmm still nothing :/
>Any other possibilities?
The suggestion offered below is a workaround that seems logical given
the lack of ORDER BY RAND() prior to MySQL 3.23. Unfortunately, it
doesn't work due to the behavior of the optimizer in pre-3.23 versions.
Specifically, MySQL notices that "rand() as rnd" is a function, deduces
(incorrectly) that the contents of the column will be constant, and thus
optimizes away the "order by rnd" clause entirely. Result: no sorting.
To deal with this, use an expression that includes RAND(), but written
in such a way that the optimizer won't think is constant. For example,
if you have an id column, do something like this:
SELECT id*0+RAND() AS rnd, ... FROM tbl_name ORDER BY rnd LIMIT 1;
>
>Rob
>----- Original Message -----
>From: "Kodrik" <[EMAIL PROTECTED]>
>To: "Rob@TH" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
>Sent: Saturday, December 08, 2001 8:45 AM
>Subject: Re: Random
>
>
>> Try
>>
>> ("select field 1, field2, field3, rand() as rnd
>> where blah blah blah
>> order by rnd limit 1")
>>
>>
>> On Saturday 08 December 2001 10:35 am, [EMAIL PROTECTED] wrote:
>> > Hi,
>> >
>> > I am trying to pull up one random name in mysql 3.22.21 but not having
>much
>> > luck.
>> > Here is what I have done:
>> > $select=$dbh->prepare("select * from $thetable where url is not null
>order
>> > by rand() limit 1");
>> > I have tried everything, but nothing seems to work. Can anyone help.
>> >
> > > Rob
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
