List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:July 12 2010 4:27am
Subject:Re: WTA Query For Winner Drawing
View as plain text  
On 7/9/2010 3:04 AM, sangprabv wrote:
> Dear List,
> I'm developing a winner drawing application using PHP+MySQL. As we know that MySQL
> has a built in function RAND() to randomize the result. But using that function will only
> randomize, my goal is the player with higher point gets higher posibility when
> randomizing. Anybody can help? Many thanks.

Here's an idea...

Perform N queries into your table and collect the PK values for all rows 
where score > rand() (adjust appropriately for the range of scores). N 
should be an appropriately large number relative to the number of 
entries you are trying to randomize.  N should be at least 1/2 of the 
total number of rows. For smaller starting sets, the value of N could 
even be several multiples of your starting set.  The more you collect, 
the more smoothly you will randomize and the more accurate the weights 
will be.

That way, each potential winner (the higher the score the better) will 
appear at most N times in the "drawing" table. Those original rows whose 
scores were never above the rand() threshold may not even appear in the 
drawing table.

 From that weighted table, you can make another random selection for the 
actual winner.

Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
WTA Query For Winner Drawingsangprabv9 Jul
  • Re: WTA Query For Winner DrawingMySQL)12 Jul