3

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!

Community
  • 1
  • 1
austinh
  • 1,061
  • 6
  • 13
  • 34

1 Answers1

1

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

select * from table order by entries*rand() desc limit 1;
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
Stephen
  • 3,341
  • 1
  • 22
  • 21
  • 1
    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. – austinh 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
  • @user Are you saying you want to randomize on the number of rows in the table? – Stephen Aug 13 '11 at 21:02
  • 1
    This gives a horribly skewed distribution.. say there are 98 rows weighted 1 and 1 row weighted 2. RAND() will produce a number between 0 and 1, so 50% of the time the number will be > 0.5. For the row weighted 2, (RAND() * 2) will be greater than 1 50% of the time. This is larger than all (RAND() * 1) results, so the row weighted 2 will be selected at least 50% of the time. It should in fact be selected 2% of the time (2/100). – Arth Oct 14 '16 at 10:54