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

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