On Sun, 16 Apr 2006, [EMAIL PROTECTED] wrote:
Thomas Chust <[EMAIL PROTECTED]> wrote:
Hello,
I have a table of strings and integer primary keys from which I would like
to retrieve a string at random. [...]
I cannot think of a more efficient way to do this if you
require each output to have equal probability. If you do
not need each string to have exactly the same probability,
however, you could do this:
SELECT string FROM strings
WHERE rowid>=random() % (SELECT max(rowid) FROM strings)
LIMIT 1;
Hello,
thank you for the tip. As the SQLite3 '%' operator does a remainder
operation not a modulo operation and as the '>=' apparently binds stronger
than the '%', I modified this query into
SELECT string FROM strings
WHERE id >= (abs(random()) % (SELECT max(id) FROM strings))
LIMIT 1;
This works very fast now and I think I can live with the somewhat less
random result. It doesn't have to have cryptographic quality ;-)
cu,
Thomas
