Select random row(s) in SQLite

by

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!

More Related Contents:

  1. Selecting more than one value
  2. Is it possible to insert multiple rows at a time in an SQLite database?
  3. How can I list the tables in a SQLite database file that was opened with ATTACH?
  4. SQLite Reset Primary Key Field
  5. How do I dump the data of some SQLite3 tables?
  6. SQLite – How do you join tables from different databases?
  7. Declare variable in SQLite and use it
  8. Does SQLite3 not support foreign key constraints?
  9. SQLite – ORDER BY RAND()
  10. Query to get records based on Radius in SQLite?
  11. How do I add a foreign key to an existing SQLite table?
  12. How to use SQL Order By statement to sort results case insensitive?
  13. Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine
  14. sqlite alter table add MULTIPLE columns in a single statement
  15. Is there AUTO INCREMENT in SQLite?
  16. Get month from DATETIME in sqlite
  17. How do I import .sql files into SQLite 3?
  18. sqlite equivalent of row_number() over ( partition by …?
  19. How to create timestamp column with default value ‘now’?
  20. Android multiple databases open
  21. Unfindable SQLite Syntax Error
  22. SQLite3 UNIQUE constraint failed error
  23. Does SQLite3 have prepared statements in Node.js?
  24. SQlite: select into?
  25. SQL error: misuse of aggregate
  26. SQLite query, ‘LIKE’
  27. How to use a calculated column by another calculated column
  28. UPSERT *not* INSERT or REPLACE
  29. Drop column from SQLite table
  30. converting int to real in sqlite

Leave a Comment