Skip to content

Instantly share code, notes, and snippets.

@swayson

swayson/sqlite_random_sample.sql

Created February 17, 2016 19:57
Show Gist options
  • Save swayson/84fc86da20db89b56eac to your computer and use it in GitHub Desktop.
Save swayson/84fc86da20db89b56eac to your computer and use it in GitHub Desktop.
Download ZIP
Efficient way to do random sampling in SQLite.
Raw
sqlite_random_sample.sql
SELECT * FROM table
WHERE _ROWID_ >= (abs(random()) % (SELECT max(_ROWID_) FROM table))
LIMIT 1
@swayson
Copy link
Author

swayson commented May 23, 2021 •
edited

Wow, this is an old gist I forgot about 😅

This picks one random row in the table by taking the first row in a random range.

Here is my proposal for random sampling multiple rows in an efficient way:
https://gist.github.com/alecco/9976dab8fda8256ed403054ed0a65d7b

Looks great and thanks for the detailed description on the implementation. Think this is great learning material and will help folks 👍

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment