List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:July 15 2011 6:42pm
Subject:Re: MySQL RAND() Issues [was Re: How to Shuffle data]
View as plain text  
I have found this approach pretty effective:

    select  *, rand() r from [mytable] where [condition] order by r limit 10

as long as you are aware that a random number is generated for every
row in the table.

 - michael dykman

On Fri, Jul 15, 2011 at 2:12 PM, Arthur Fuller <fuller.artful@stripped> wrote:
> This would be sooo much simpler to solve in MS-SQL, given the function
> NewID(), which is guaranteed to return a unique value. I have used this in a
> few web sites and it works splendidly; something along the lines of
>
> SELECT TOP 10 *, NewID() FROM User_Messages
> ORDER BY NewID
>
> which is guaranteed to produce a new GUID for each row, and then order by
> said values. It is not guaranteed to produce 10 results different than the
> previous SELECT, but nevertheless works extremely well on a web site.
>
> I have Googled this and that but not yet succeeded in finding the equivalent
> in the MySQL world. In theory, this should be relatively easy, since the
> GUID docs are available, but I'm out of my depth here on how to write the
> MySQL equivalent function.
>
> In case anyone has devised a MySQL equivalent for the NewID() function, we
> could all massively benefit from your posting of same.
>
> TIA,
> Arthur
>
> On Fri, Jul 15, 2011 at 1:40 PM, Jan Steinman <Jan@stripped> wrote:
>
>> > From: Reindl Harald <h.reindl@stripped>
>> >
>> > do not use any random-functions of mysql even if they exists
>> > http://bugs.mysql.com/bug.php?id=59253
>>
>> Of course, it depends on the desired quality of randomness needed.
>>
>> I'm using RAND() to select random quotations to put at the end of emails. I
>> can easily repeat the process by re-selecting the "Signature:" menu in Apple
>> Mail. Problem is, I often notice that doing so cycles through several
>> similar signatures in a decidedly non-random way!
>>
>> (You can demo this by sending email to <Quote@stripped>, with a
>> search term in the Subject: line.)
>>
>> But of course, a nuclear plant is not going to melt down because of my
>> signature line.
>>
>> So RAND() can be useful, but it is not really very random, and should be
>> used with caution. My guess is that it's using Knuth's linear congruential
>> algorithm that has well-known problems, but that unfortunately has been
>> hidden deep in system code libraries since "Fundamental Algorithms" was
>> published in 1968.
>>
>> ----------------
>> World events tend to be driven by loose coalitions of economic, political,
>> and military interests, which function like guilds of species in an
>> ecosystem. These guilds generate patterns of events that meet the interests
>> of these coalitions, without there being any unity of purpose or clear plan.
>> When powerful players accept they are not all-powerful, they increase their
>> effectiveness, but are also able to deny and cover any responsibility for
>> the adverse outcomes of those actions. -- David Holmgren
>> :::: Jan Steinman, EcoReality Co-op ::::
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=1
>>
>>
>



-- 
 - michael dykman
 - mdykman@stripped

 May the Source be with you.
Thread
MySQL RAND() Issues [was Re: How to Shuffle data]Jan Steinman15 Jul
  • Re: MySQL RAND() Issues [was Re: How to Shuffle data]Arthur Fuller15 Jul
    • Re: MySQL RAND() Issues [was Re: How to Shuffle data]Michael Dykman15 Jul
    • Re: MySQL RAND() Issues [was Re: How to Shuffle data]Dan Nelson15 Jul
  • Re: MySQL RAND() Issues [was Re: How to Shuffle data]Reindl Harald15 Jul