Return random rows from `SELECT` in CockroachDB

Question

How can I select random rows from a SQL table using CockroachDB? For example, if I have a number of questions and I want to generate a different sequence each time a student loads them.

Kokaras

Answer 1

CockroachDB doesn't offer an efficient way to do this yet! For a non-efficient way you can use SELECT ... FROM ... ORDER BY random() LIMIT 1;

Alternatively, you can handle shuffling the results of a SELECT statement in your application itself. After putting the results into an array (or any other aggregate-like structure), you can also shuffle the order there.

Kokaras

Answer 2

I'm using the following statement to select random number of rows from cockroach db.

SELECT ... FROM ... WHERE round(random()*10) % 10 = 0 LIMIT 10

Homeo

Answer 3

For a more efficient way you can add an integer column randomid to each row. When inserting put a random number in that column. Then you can retrieve a random column with:

SELECT ... FROM ... WHERE randomid >= ? ORDER BY randomid LIMIT 1;

where ? is a random number.

Note that you will need extra storage for the random number and you must index the randomid column.

Also note that you may need to run this query twice (>= and <) to ensure you get a result. Though the probability of striking out on the first query shoud be very low.

Bellboy