Menu
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?
461
select * from foo where rowid = (abs(random()) % (select (select max(rowid) from foo)+1));
To get a single row randomly, we can use the LIMIT Clause and set to only one row. ORDER BY clause in the query is used to order the row(s) randomly. It is exactly the same as MYSQL. Just replace RAND( ) with RANDOM( ).
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!
107
SELECT * FROM table ORDER BY RANDOM() LIMIT X If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
