3 ms·

I like your query as a portable version of the written function, but I also think that it could fail to yield the results probably intended before that mess was

I like your query as a portable version of the written function, but I also think that it could fail to yield the results probably intended before that mess was written. If the intent is actually to select one, and only one, random row from the table than this Stack Overflow entry has a result that is in most cases in the direction I was thinking (LIMIT 1, and have some way of selecting a random entry, remembering that the ID field might be monotonic, but isn't assured to be continuous (have no gaps)) https://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql https://stackoverflow.com/questions/19412/how-to-request-a-r... Unfortunately it seems that it depends on language extensions that are not portable. EDIT: It unfortunately also appears that the easiest query scales poorly to large tables as it works by assigning EACH row a random ID and then limiting it to one of those by some order (probably lowest assigned row). A more effective approach might be a function or procedure based around a pre-recorded 'maximum' ID for the column (assuming it's vaguely monotonic and not a GUID), picking a random result between base and that ID, Verifying it exists, and if it doesn't trying again up to N times before using a fallback procedure. (E.G. after 100 random stabs that fail selecting ~500 records in the range of lastRND +/- 250 and using the ORDER BY rand() or equivalent on that sub-set). If even /that/ fails pick the next or previous record by which is closest.

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 ago
  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.

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 ago
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.