Select random row(s) in SQLite

Question

In MySQL, you can select X random rows with the following statement:

SELECT * FROM table ORDER BY RAND() LIMIT X 

This does not, however, work in SQLite. Is there an equivalent?

Answer 1

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!

Answer 2

SELECT * FROM table ORDER BY RANDOM() LIMIT X