"Martin Gallagher" <[EMAIL PROTECTED]> wrote on 04/16/2005 
08:49:35 AM:

> Hi,
> 
> 
> 
> I have the following query:
> 
> 
> 
> SELECT person FROM people WHERE ORDER BY RAND() * (1 / score) LIMIT 1
> 
> 
> 
> This returns a person randomly, but the chance of the person being 
selected
> is increased with a higher `score`.
> 
> 
> 
> What I want to do is make a single query that will return 10 results in 
this
> fashion.
> 
> 
> 
> Currently I'm doing:
> 
> 
> 
> for (i = 1; i <= 10; i++) do: SELECT person FROM people WHERE ORDER BY
> RAND() * (1 / score) LIMIT 1
> 
> 
> 
> Which returns a results like:
> 
> 
> 
> Mike
> 
> Sam
> 
> Sam
> 
> Mike
> 
> Mike
> 
> Mike
> 
> John
> 
> Sam
> 
> Mike
> 
> John
> 
> 
> 
> This is exactly the result I desire, but programmatically it's not the 
most
> efficient way. I'm guessing using 1 query and using the result set is 
MUCH
> faster, 1 query... 1 result, instead of 10.
> 
> 
> 
> I have tried:
> 
> 
> 
> (SELECT person FROM people WHERE ORDER BY RAND() * (1 / score) LIMIT 1)
> UNION (X) UNION (X) ... [repeat ten times]
> 
> 
> 
> This however returns this kind of result:
> 
> 
> 
> Mike
> 
> Sam
> 
> John
> 
> 
> 
> The UNION query seems to remove the repeats, and because this is a
> mathematical system this will throw it out.
> 
> 
> 
> This of course is not what I want :-(. Is there an option that allows
> repeats?
> 
> 
> 
> Cheers,
> 
> - Martin
> 


You are correct in saying that a UNION query eliminates repeats. However a 
UNION ALL does not. Just add the ALL keyword where appropriate and you 
should get the results you wanted.

http://dev.mysql.com/doc/mysql/en/union.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

PS - I think this "weighted random choice" routine would perform better if 
you randomly picked a name from a 3 member array in your source code. You 
can still use the score to adjust the probability of picking and you won't 
have to do 10 queries to get a 10 name list. You only do a single query 
and re-use the results. Just my $.02 - SG

Reply via email to