3 ms·
I think that something like select * from the_table offset rand(select count(*) from the_table) limit 1; would do the trick with least bias, though not
by nine_k 8y ago
I think that something like
select * from the_table
offset rand(select count(*) from the_table)
limit 1;
would do the trick with least bias, though not necessarily very fast.
- mjevans 8y agoThe offered example query is could return no results in the presence of record gaps / deleted records; also the OFFSET value should probably be computed within a sub-query, not exposed. A corrected version based on being >= or <= the random number (picking the nearest) COULD work (in fact, that's the way my suggest final fallback works), but would still be biased. That was the reason I suggested taking a limited number of stabs at random numbers within the allowed range; my example still has some slight bias (a little in the 'best case' fallback, and a lot in the absolute fallback), but it tries to deliver a truly random result first, many times.