SQLite: Randomly order rows

Return rows or records in a table with random order in SQLite

Let's take a simple table for this:

CREATE TABLE user (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);
INSERT INTO user (name) VALUES ('abc'), ('def'), ('ghi'), ('jkl'), ('mno');

SELECT * FROM user ORDER BY random();

This will return the rows in a random order

You can limit the number of rows

SELECT * FROM user ORDER BY random() LIMIT 5;

We can even randomly shuffle a subset of rows with limit and where clauses

SELECT * FROM user
WHERE id BETWEEN 1 and 10 ORDER BY random() limit 5;

This is cool if you want to get certain subset of samples but in no strict order.

Read the full post with interactive SQL codeblocks and playground like environment here.

SQLite: Randomly order rows | Meet Gor

meetgor.com