Find weighted random row. Or split 1 row into many based on column value I want to query the DB and pick a random item weighted by the count. Something like so:

SELECT * FROM (-- SUBQUERY THAT GENERATES WEIGHTED TABLE --)
    ORDER BY ct * RAND() LIMIT 1


I'm thinking the following might be a good approach.  I have a table that looks something like this:

| id  | item   | count |
| --- | ------ | ----- |
| 1   | item a | 3     |
| 2   | item b | 2     |
| 3   | item c | 4     |


How can I query the database so that 1 row is shown per count? I.e. like this:

| id  | item   | count |
| --- | ------ | ----- |
| 1   | item a | 3     |
| 1   | item a | 3     |
| 1   | item a | 3     |
| 2   | item b | 2     |
| 2   | item b | 2     |
| 3   | item c | 4     |
| 3   | item c | 4     |
| 3   | item c | 4     |
| 3   | item c | 4     |


In case it helps, I've created a DB fiddle: https://www.db-fiddle.com/f/wRZgBYkDM18c5fk7tgkBkA/0

I'm using MariaDB 10.0.
Instead of proliferating the number of rows, let's add a cumulative total:

| id  | item   | count | cumm_tot
| --- | ------ | ----- |
| 1   | item a | 3     |  3
| 2   | item b | 2     |  5
| 3   | item c | 4     |  9


Then get the last number, 9, for use below.
Index the new column.
Fetch random item thus:

WHERE    cumm_tot &gt; 9 * RAND()
ORDER BY cumm_tot
LIMIT 1


To set the column and get the 9, perform this task:

SET @c := 0;
UPDATE tbl
    SET cumm_tot = (@c := @c + `count`)
    ORDER BY id;
-- @c will now be the total (9)


Whenever you add items or change the weights, rerun the two statements above.

(This code should work on any version of MySQL/MariaDB.  There may be a "window function" that works better in new versions.)