Select random rows 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 work, however, in SQLite. Is there an equivalent?

Answer 1

For better, better performance, use:

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

SQL engines first load the projected row fields into memory and then sort them , here we just randomly sort the id field of each row that is in memory because it is indexed and then separates X from them and find the entire row using these are X identifiers.

Thus, it reduces the amount of RAM and processor as the table grows!

Answer 2

SELECT * FROM table ORDER BY RANDOM() LIMIT X

Answer 3

 SELECT * FROM table ORDER BY RANDOM() LIMIT 1 

Answer 4

All answers here are based on ORDER BY . This is very inefficient (i.e. not applicable) for large sets. Why not just put abs(CAST(random() AS REAL))/9223372036854775808 < 0.5 in the WHERE to get a chance of 0.5 hits in this case?

 SELECT * FROM table WHERE abs(CAST(random() AS REAL))/9223372036854775808 < 0.5 

A large number is the maximum absolute number that random() can express. abs() is that it is signed. The result is a uniformly distributed random variable between 0 and 1.