Select Random Row(S) in SQLite

Select random row(s) in SQLite

For a much better performance use:

SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RANDOM() LIMIT x)

SQL engines first load projected fields of rows to memory then sort them, here we just do a random sort on id field of each row which is in memory because it's indexed, then separate X of them, and find the whole row using these X ids.

So this consume less RAM and CPU as table grows!

Get random rows from sqlite database, then sort by column

First get the 10 random rows in a subquery and then sort them by difficulty:

SELECT *
FROM (
  SELECT id, difficulty
  FROM tasks
  ORDER BY random()
  LIMIT 10
)
ORDER BY difficulty;

sql query to select a random row from a table based on the previous query

I have found this is not possible on a database that doesn't support stored procedures like sqlite unfortunately.

SQLite Android Select Random Row Excluding Certain Columns

Here is one option, which assumes that the C1 column determines the ordering:

SELECT Col2, Col4
FROM yourTable
WHERE C1 <> (SELECT MIN(C1) FROM yourTable) AND
      C1 <> (SELECT MAX(C1) FROM yourTable)
ORDER BY RANDOM()
LIMIT 1;

Python Sqlite: How would you randomly select a value-specific row?

No python specific syntax is needed: sqlite has a random() function:

select
  *
from users

where gender == 'M'

order by random()

limit 1

For performance see this: https://stackoverflow.com/a/24591688/788700

Get random record in SQLite

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;

---

Related Topics