Keeping a random order stable in SQLite

SQLite's built-in random() function does not expose a seed. Using it directly in an ORDER BY expression can produce a different order on each query.

To keep a random ordering between queries, store a random value in each row instead.

Assuming the questions table already has a random_order column:

UPDATE questions SET random_order = Random();

Then sort by the stored values:

SELECT * FROM questions ORDER BY random_order

The order stays the same while those values remain unchanged. Running the update again assigns a new order. Equal values would need a secondary sort key if an exact total order matters.

This does not make an ephemeral filesystem persistent. If the SQLite database is lost on restart, its stored ordering is lost too. It is useful for keeping non-critical ordering stable during the lifetime of that database.