This should do the trick:
WITH CTE AS ( SELECT random() * (SELECT SUM(percent) FROM YOUR_TABLE) R ) SELECT * FROM ( SELECT id, SUM(percent) OVER (ORDER BY id) S, R FROM YOUR_TABLE CROSS JOIN CTE ) Q WHERE S >= R ORDER BY id LIMIT 1;
Q gives the following result:
1 50 2 85 3 100
We then simply generate a random number in range [0, 100) and pick the first row that is at or beyond that number (the
WHERE clause). We use common table expression (
WITH) to ensure the random number is calculated only once.
SELECT SUM(percent) FROM YOUR_TABLE allows you to have any weights in
percent – they don’t strictly need to be percentages (i.e. add-up to 100).