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.
> 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:
- michael dykman
May the Source be with you.