Get random record in SQLite

Question

I fetch records in my SQLite database like this.

spellId = extra.getString("spellId");
DBase db = new DBase(this);
db.open();
String[] data = db.getRecord(Integer.parseInt(spellId));
db.close();

Can I get random data like this without using raw queries and cursor?

possible duplicate of [Select random row from an sqlite table](http://stackoverflow.com/questions/2279706/select-random-row-from-an-sqlite-table) — Gopal00005, Apr 12 '15 at 11:19

score 13 · Answer 1 · answered Jan 31 '15 at 07:43

13

try like this:

db.rawQuery("SELECT * FROM mainTable ORDER BY RANDOM() LIMIT 1", null);

answered Jan 31 '15 at 07:43

Rathan Kumar

2,567
2
17
24

This would work but is not recommended for large tables. `Order By Random()` doesn't scale well with the number of rows. – Ravi K Thapliyal Jan 31 '15 at 07:58

Ravi K Thapliyal · Accepted Answer · 2015-01-31T08:23:54.783

You can use Random#nextInt() like

String[] data = db.getRecord(new Random().nextInt(num));

where num falls in the range of your record IDs. You would need to adapt this solution in case your Ids are fragmented and do not form a consecutive range.

One of the ways to do that would be to first create a query to fetch all the primary keys and store the values in a set somewhere. Then pick a random key by generating an index using Random.

String[] data = db.getRecord(IDSet.get(new Random().nextInt(IDSet.size())));

Check out the docs for more information.

Returns a pseudorandom, uniformly distributed int value between 0 (inclusive) and the specified value (exclusive), drawn from this random number generator's sequence.

If you're considering a DB query solution

A better alternative to using ORDER BY RANDOM() (which is known to not scale well as the number of rows in your table grows) is to let SQLite return a random row using an OFFSET.

First save the total number of rows num somewhere.

SELECT COUNT(*) AS num FROM spells;

Then choose a random number rnum between (0, num) using Random and use the query

SELECT * FROM spells LIMIT 1 OFFSET rnum;

But the number of records in my database isn't constant. Is there an equivalent for the getCount() of cursor in this way of accessing the db? — Suika, Jan 31 '15 at 07:13
Is `spellId` the primary key of the table or does it work like an index in a list of fetched db records? — Ravi K Thapliyal, Jan 31 '15 at 07:15
It is. I fetch it using a Bundle from the previous activity. — Suika, Jan 31 '15 at 07:17

Get random record in SQLite

2 Answers2

If you're considering a DB query solution