CODEWITHSUNDEEP
mysql
Fuad Chonora
Fuad Chonora

Reputation: 19

Selecting Rows by percentage of chance

This is the table structure and it only contains these two rows:

--------------------------
|id   | item  | priority |
--------------------------
|1    | item1 | 20       |
--------------------------
|2    | item2 | 30       |
--------------------------

I have to write a query on the table to get any of these 2 rows or nothing by percentage of chance.

20% chance to get item1

30% chance to get item2

50% chance to get nothing

Upvotes: 0

Views: 329

Answers (2)

Stepan Kolesnik
Stepan Kolesnik

Reputation: 861

Below works on MySQL, MariaDB and MS SQL Server.

SELECT id, item, priority
FROM (
  SELECT rand() * 100 as rnd
) r
INNER JOIN (
  SELECT *, (SELECT COALESCE(SUM(priority), 0) FROM my_table WHERE id < t.id) as range_start
  FROM my_table t
) mt
ON r.rnd between mt.range_start and (mt.priority + mt.range_start);

PostgreSQL
replace rand() with random().

SQLite
replace rand() * 100 with ABS(RANDOM() % 100).

Oracle
replace SELECT rand() * 100 as rnd with SELECT dbms_random.value() * 100 as rnd FROM DUAL.

Upvotes: 1

danblack
danblack

Reputation: 14736

Using MySQL-8.0+/MariaDB-10.2+ window functions:

SELECT item
FROM
  (SELECT item,
          SUM(priority) OVER (
                              ORDER BY id) AS cum_prob
   FROM my_table) AS b
WHERE cum_prob >= RAND()*100
ORDER BY cum_prob
LIMIT 1

ref: fiddle

Upvotes: 0

Related Questions