List:General Discussion« Previous MessageNext Message »
From:Haksun Li Date:April 24 2003 9:52pm
Subject:RE: ORDER BY RAND() question
View as plain text  
The correct way to do it is:

ORDER BY RAND(NOW());



Haksun

>-----Original Message-----
>From: Jason [mailto:jter-lists@stripped]
>Sent: Thursday, April 24, 2003 5:38 PM
>To: Verdon vaillancourt; mysql@stripped
>Subject: Re: ORDER BY RAND() question
>
>I believe LIMIT applies before the ORDER BY....
>http://www.mysql.com/doc/en/LIMIT_optimisation.html
>So you will always get the same 2 results, the first one that mySQL
finds...
>but they probably appear in random order LOL.
>
>----- Original Message -----
>From: "Verdon vaillancourt" <verdon@stripped>
>To: <mysql@stripped>
>Sent: Wednesday, April 23, 2003 8:04 PM
>Subject: ORDER BY RAND() question
>
>
>> Hi :)
>>
>> I'm having trouble with ORDER BY RAND() not being very random. I've
done
>> some searching in the archives and I gather I'm not alone. I've
quoted a
>> couple threads I've researched and tried (without success) below. I'm
>sort
>> of hoping someone could point me in the right direction. I'm using
mySql
>> 3.23.54 on linux.
>>
>> This is my query via a php script...
>>
>> mysql_query("SELECT ID, Title FROM listingsDB WHERE (featured =
'yes')
>ORDER
>> BY RAND() LIMIT 2");
>>
>> The query returns the first (and same) 2 rows every time and never
seems
>> random. The following threads offer some suggestions that looked
>promising,
>> but I couldn't puzzle out...
>>
>> 1 =====
>> On Tue, Feb 11, 2003 Sergei suggests...
>> http://www.listsearch.com/mysql.lasso?id=286581
>>
>> // quote
>> RAND() initialization for new connection isn't very random, so first
few
>> rand() values differ only slightly.
>>
>> It was fixed in 4.0. I will backport the fix to 3.23. You can either
>upgrade
>> to MySQL 4.0, or wait till next 3.23 release, or use a simple
workaround:
>> run
>>
>> do benchmark(10,rand());
>>
>> just after establishing connection - before first SELECT.
>> // endquote
>>
>> I'm not sure how to work this into my query properly. I've tried
running
>it
>> directly in the db via phpMyAdmin, and then loading the page with my
>query,
>> but it still never seems random.
>>
>> 2 =====
>> Another thread on Fri, Sep 20, 2002
>> http://www.listsearch.com/mysql.lasso?id=274549
>>
>> Suggests doing a rand() in a different manner altogether. I've tried
a
>few
>> variations of working this into my query without any success, just
>errors ;)
>>
>> // quote
>> select truncate(rand()*count(*),0) from TABLE;
>> Instead of
>> select ID from table ORDER BY rand() LIMIT 1
>> // endquote
>>
>>
>> Thanks and best regards,
>> verdon
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>lists@stripped
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>http://lists.mysql.com/mysql?unsub=1

Thread
ORDER BY RAND() questionVerdon vaillancourt24 Apr
  • Re: ORDER BY RAND() questionJason24 Apr
    • RE: ORDER BY RAND() questionHaksun Li24 Apr
  • re: ORDER BY RAND() questionEgor Egorov25 Apr
Re: ORDER BY RAND() questionVerdon vaillancourt24 Apr
Re: ORDER BY RAND() questionVerdon Vaillancourt27 Apr