Take the 2-minute tour ×

Possible Duplicate:
MySQL: Select Random Entry, but Weight Towards Certain Entries

I have a table that I would like to be able to randomly select a user from, but in this table I have an 'Entries' column. I have been able to randomly select a user, but now I am trying to take into account their number of entries (higher the number, the more likely they are to win). How would I go about doing this?

My table looks something like:

FN   | LN    | ENTRIES
Bob  | Smith | 20
John | Doe   | 3

Thanks for your help!

share|improve this question

marked as duplicate by Brad, Karoly Horvath, Vincent Savard, ajreal, genesis Aug 13 '11 at 21:04


it isnt really random if someone has more of a chance to be selected. –  Hunter McMillen Aug 13 '11 at 20:28
[Check this thread][1] [1]: stackoverflow.com/questions/2716163/… –  Salvatore Previti Aug 13 '11 at 20:30
@Hunter: it is. it's just not uniformly distributed. –  Karoly Horvath Aug 13 '11 at 20:31
add comment

1 Answer

up vote 1 down vote accepted

To weight them, multiply rand() by the entries column..

select * from table order by entries*rand() desc limit 1; 
share|improve this answer
It's worth saying that this is very slow for big tables, as it has to generate a random number for every row of the table. –  Vincent Savard Aug 13 '11 at 20:32
@vincent It won't work by itself if the developer wants to weight them by the value of the entries column in the table. –  Stephen Aug 13 '11 at 20:33
Yes, I misread the question and edited my previous comment. –  Vincent Savard Aug 13 '11 at 20:34
I think I am having troubles getting it to work. I have inserted 2 rows, one has 2 entries while the other has 300 entries. I has picked the one with fewer entries more often than the other one. –  austinhollis Aug 13 '11 at 20:46
@user790068: it is to be expected. Lower values will be selected before higher in ascending order. You need to add DESC keyword after entries*rand() –  Mchl Aug 13 '11 at 20:54
show 2 more comments
