How to select one row randomly taking into account a weight?

by

I think the simplest is actually to use the weighted reservoir sampling:

SELECT
  id,
  -LOG(RAND()) / weight AS priority
FROM
  your_table
ORDER BY priority
LIMIT 1;

It’s a great method that lets you choose M out of N elements where the probability to be chosen for each element is proportional to its weight. It works just as well when you happen to only want one element.
The method is described in this article. Note that they choose the biggest values of POW(RAND(), 1/weight), which is equivalent to choosing the smallest values of -LOG(RAND()) / weight.

More Related Contents:

  1. quick selection of a random row from a large table in mysql
  2. quick selection of a random row from a large table in mysql
  3. Recreating a query using INNER JOIN? [duplicate]
  4. What is better subquery vs literal on IN clause in mysql
  5. SQL query return data from multiple tables
  6. MySQL select 10 random rows from 600K rows fast
  7. MySQL SELECT only not null values
  8. Ordering by specific field value first
  9. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  10. Simple Random Samples from a Sql database
  11. Checking multiple columns for one value
  12. How does MySQL’s ORDER BY RAND() work?
  13. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  14. MySQL INSERT INTO … VALUES and SELECT
  15. MySQL error 1241: Operand should contain 1 column(s)
  16. Compare only day and month with date field in mysql
  17. Syntax error when using except in a query
  18. SELECT query return 1 row from each group
  19. MySQL INNER JOIN select only one row from second table
  20. Simple Random Samples from a MySQL Sql database
  21. MySQL Select Query – Get only first 10 characters of a value
  22. SQL query to check if a name begins and ends with a vowel
  23. Insert/ Update random date in MySQL
  24. How to SELECT by MAX(date)?
  25. MYSQL Select One Random record from each Category
  26. mysql unique number generation
  27. SELECTING with multiple WHERE conditions on same column
  28. How to find gaps in sequential numbering in mysql?
  29. MySQL: Invalid use of group function
  30. How to select data where a field has a min value in MySQL?

Leave a Comment