Getting random value from a SQLite table

Question

I am currently working on an Android app and I am trying to get a random value from a database table that looks like this:

| ID | Score|
| 1  | 20   |
| 2  | 5    |
| 5  | 5    |
| 6  | 5    |
| 14 | 15   |

(lets assume these are the only values in the table)

I would like to get a random value from this table that has ID 1 40% of the time, ID 2 10% of the time, ID 5 10% of the time and so on...
If that is even possible, how would you go about it?

Why not use a hashtable instead of SQLite? Does the data need to persist? — Aidanc, Apr 09 '12 at 01:53
Also, if those are the only values in the database.. and ID 1 = 40% , {2,5,6,14} = 10% each.. what happens the other 20% of the time? This question needs further explaining. — Aidanc, Apr 09 '12 at 01:55
@Aidanc: I think the Score is actually a "relative" counter, so they all sum up to 100. So 14 is actually 30% since the value is 15. But I'm only guessing here... — Patrick, Apr 09 '12 at 01:57
This data is a list of song IDs (some of which are to be played more often) — deluxe, Apr 09 '12 at 03:26

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

Given a List<Integer>, add() each song index score times, Collections.shuffle() the list, and play in order. For better results, skip successive duplicates. More here.

edited May 23 '17 at 12:29

Community

1
1

answered Apr 09 '12 at 04:10

trashgod

203,806
29
246
1,045

score 0 · Answer 2 · edited May 23 '17 at 11:44

There is no native support from any of the known databases for a random select. IF you do some searches you might find something like:

select * from my_table order by rand();

But that is woefully inefficient as it attempts to sort the entire table.

If your data set is not too big, you can simply pull the entire table into an array and select randomly from that array.

Now wieghted selection, that is more tricky.

A simpler version would work out something like this:

Get a list of all of the ids (id) and weights (weight) in the table.
Iterate through the list and calculate the cumulative weight cwieght for each id.
When you reach the end of the list you will have a total of all of the weights. Select a random number r between 0 and that total.
Scan through the list again and find the item where r >= cweight && r < cweight+weight You can use a binary chop search for this if you are game.

There are other smarter approaches to this as well, which depend on certain restrictions.

Getting random value from a SQLite table

2 Answers2

Linked