"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
