Select random row from a sqlite table

Question

I have a sqlite table with the following schema:

CREATE TABLE foo (bar VARCHAR)

I'm using this table as storage for a list of strings.

How do I select a random row from this table?

Answer 1

Have a look at Selecting a Random Row from an SQLite Table

SELECT * FROM table ORDER BY RANDOM() LIMIT 1;

Answer 2

The following solutions are much faster than anktastic's (the count(*) costs a lot, but if you can cache it, then the difference shouldn't be that big), which itself is much faster than the "order by random()" when you have a large number of rows, although they have a few inconvenients.

If your rowids are rather packed (ie. few deletions), then you can do the following (using (select max(rowid) from foo)+1 instead of max(rowid)+1 gives better performance, as explained in the comments):

select * from foo where rowid = (abs(random()) % (select (select max(rowid) from foo)+1));

If you have holes, you will sometimes try to select a non-existant rowid, and the select will return an empty result set. If this is not acceptable, you can provide a default value like this :

select * from foo where rowid = (abs(random()) % (select (select max(rowid) from foo)+1)) or rowid = (select max(rowid) from node) order by rowid limit 1;

This second solution isn't perfect : the distribution of probability is higher on the last row (the one with the highest rowid), but if you often add stuff to the table, it will become a moving target and the distribution of probabilities should be much better.

Yet another solution, if you often select random stuff from a table with lots of holes, then you might want to create a table that contains the rows of the original table sorted in random order :

create table random_foo(foo_id);

Then, periodicalliy, re-fill the table random_foo

delete from random_foo;
insert into random_foo select id from foo;

And to select a random row, you can use my first method (there are no holes here). Of course, this last method has some concurrency problems, but the re-building of random_foo is a maintainance operation that's not likely to happen very often.

Yet, yet another way, that I recently found on a mailing list, is to put a trigger on delete to move the row with the biggest rowid into the current deleted row, so that no holes are left.

Lastly, note that the behavior of rowid and an integer primary key autoincrement is not identical (with rowid, when a new row is inserted, max(rowid)+1 is chosen, wheras it is higest-value-ever-seen+1 for a primary key), so the last solution won't work with an autoincrement in random_foo, but the other methods will.

Answer 3

You need put "order by RANDOM()" on your query.

Example:

select * from quest order by RANDOM();

Let's see an complete example

1. Create a table:

CREATE TABLE  quest  (
    id  INTEGER PRIMARY KEY AUTOINCREMENT,
    quest TEXT NOT NULL,
    resp_id INTEGER NOT NULL
);

Inserting some values:

insert into quest(quest, resp_id) values ('1024/4',6), ('256/2',12), ('128/1',24);

A default select:

select * from quest;

| id |   quest  | resp_id |
   1     1024/4       6
   2     256/2       12
   3     128/1       24
--

A select random:

select * from quest order by RANDOM();
| id |   quest  | resp_id |
   3     128/1       24
   1     1024/4       6
   2     256/2       12
--

If you want to return only one row

select * from quest order by RANDOM() LIMIT 1;
| id |   quest  | resp_id |
   2     256/2       12
--

Answer 4

What about:

SELECT COUNT(*) AS n FROM foo;

then choose a random number m in [0, n) and

SELECT * FROM foo LIMIT 1 OFFSET m;

You can even save the first number (n) somewhere and only update it when the database count changes. That way you don't have to do the SELECT COUNT every time.

Answer 5

Here is a modification of @ank's solution:

SELECT * 
FROM table
LIMIT 1 
OFFSET ABS(RANDOM()) % MAX((SELECT COUNT(*) FROM table), 1)

This solution also works for indices with gaps, because we randomize an offset in a range [0, count). MAX is used to handle a case with empty table.

Here are simple test results on a table with 16k rows:

sqlite> .timer on
sqlite> select count(*) from payment;
16049
Run Time: real 0.000 user 0.000140 sys 0.000117

sqlite> select payment_id from payment limit 1 offset abs(random()) % (select count(*) from payment);
14746
Run Time: real 0.002 user 0.000899 sys 0.000132
sqlite> select payment_id from payment limit 1 offset abs(random()) % (select count(*) from payment);
12486
Run Time: real 0.001 user 0.000952 sys 0.000103

sqlite> select payment_id from payment order by random() limit 1;
3134
Run Time: real 0.015 user 0.014022 sys 0.000309
sqlite> select payment_id from payment order by random() limit 1;
9407
Run Time: real 0.018 user 0.013757 sys 0.000208

Answer 6

SELECT   bar
FROM     foo
ORDER BY Random()
LIMIT    1

Answer 7

I came up with the following solution for the large sqlite3 databases:

SELECT * FROM foo WHERE rowid = abs(random()) % (SELECT max(rowid) FROM foo) + 1; 

The abs(X) function returns the absolute value of the numeric argument X.

The random() function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

The operator % outputs the integer value of its left operand modulo its right operand.

Finally, you add +1 to prevent rowid equal to 0.