Returns random rows from `SELECT` in CockroachDB
CockroachDB doesn't offer an efficient way to do this yet! For an inefficient way, you can useSELECT ... FROM ... ORDER BY random() LIMIT 1;
Alternatively, you can handle the shuffling of statement results SELECT
in the application itself. After putting the results into an array (or any other aggregate-like structure), you can also shuffle the order there.
source to share
For a more efficient way, you can add an integer column randomid
to each row. When pasting, put a random number in this column. Then you can get a random column with:
SELECT ... FROM ... WHERE randomid >= ? ORDER BY randomid LIMIT 1;
Where? is a random number.
Note that you will need additional storage for the random number and you must index the column randomid
.
Also note that you may need to run this query twice (> = and <) to make sure you get the result. Although the probability of deleting the first query will be very low.
source to share