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. The best solution I could think of was to > first do a > SELECT count(id) FROM strings; > and then a > SELECT string FROM strings LIMIT 1 OFFSET ?; > where the parameter is set to an integer in the range [0, count(id)[. This > is not exactly very fast, though. I guess because the second statement > steps through all the rows up to the one that is to be returned. > > I can't really use a random primary key to look up a value, because not > every key in the interval [0, count(id)[ is necessarily in use. > > And all the constructions I tried that involved random() or a randomly > filled column in an ORDER BY clause yield even worse performance than the > above approach. > > Does anybody have a good idea how this could be implemented more > efficiently? >
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; -- D. Richard Hipp <[EMAIL PROTECTED]>
