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.

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.

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