Re:Nondeterministic Functions in MySQL (i.e. Rand) Can Surpr...
by nine_k on Thursday December 06, 2018 @04:28PM
(#18622433)
I think that something like
would do the trick with least bias, though not necessarily very fast.
[ Parent
| on HN ]
Re:Nondeterministic Functions in MySQL (i.e. Rand) Can Surpr...
by mjevans on Thursday December 06, 2018 @04:42PM
(#18622561)
The 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.