"Martin Gallagher" <martin.gallagher@stripped> 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
