CODEWITHSUNDEEP
javaandroidsqlite
deluxe
deluxe

Reputation: 13

Getting random value from a SQLite table

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?

Upvotes: 1

Views: 1105

Answers (2)

Michael Slade
Michael Slade

Reputation: 13877

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:

  1. Get a list of all of the ids (id) and weights (weight) in the table.
  2. Iterate through the list and calculate the cumulative weight cwieght for each id.
  3. 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.
  4. 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.

Upvotes: 0

trashgod
trashgod

Reputation: 205785

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.

Upvotes: 1

Related Questions