Select random row from a sqlite table

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

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

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.

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

Example:

select * from quest order by RANDOM();

Let's see an complete example

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
--

*Each time you select, the order will be different.

If you want to return only one row

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

*Each time you select, the return will be different.

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.

Select random row from a sqlite table

Related

Recent Posts