1

--RESOLVED--

Say you have a table for entries, where entry1 has a weight of 20, and entry2 with a weight of 80. Running a query like

SELECT * FROM `entries` ORDER BY RAND() LIMIT 1

will return either of the row at random. However, I wanted to factor in weight, such that entry2 has 80% chances of being drawn and entry1 has 20%.

Is there a proper/hacky way of writing this query? Previously, I had to insert 20 entries for entry1 and 80 entries for entry2 just to have each weight well represented.

foxlance
  • 73
  • 6
  • 1
    possible duplicate of [MySQL: Select Random Entry, but Weight Towards Certain Entries](http://stackoverflow.com/questions/2417621/mysql-select-random-entry-but-weight-towards-certain-entries) (**found by Googling "*mysql random weighted*"**) – h2ooooooo Oct 22 '14 at 12:00
  • Thanks for pointing this out. My test result looks promising. However, even with the explanation on that page, I don't understand how it worked (particularly the part where you divide by weight, what was the value of weight when the query executed?) – foxlance Oct 22 '14 at 12:18
  • (*I'm not ignoring you, but I don't know enough about logarithmic math to answer you - I just found the duplicate*). That said, [this comment](http://stackoverflow.com/questions/2417621/mysql-select-random-entry-but-weight-towards-certain-entries#comment23635801_12301949) seems to explain it. – h2ooooooo Oct 22 '14 at 12:19
  • Thanks h2ooooooo! Appreciate it. Marked the question as resolved. – foxlance Oct 22 '14 at 12:22

1 Answers1

0

I believe the following will do what you want:

select *
from entries e
order by weight * rand()
limit 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786