I’m not entirely sure your solution will have an even distribution. It depends a lot on how many times random() is called (once per row vs once per sort operation), and how the sort algorithm works. I might do this instead:
SELECT * FROM (SELECT random(), t.* FROM t) ORDER BY 1 LIMIT 200; As this makes sure random() is only called once per row. I’m pretty sure this is actually equivalent to yours, but it makes things a bit more explicit. If you only needed one, I’d do something like: SELECT * FROM table ORDER BY rowid LIMIT 1 OFFSET abs(random()) % (SELECT count(*) FROM table); Or even just call that multiple times if you need a moderate number. The ORDER BY should be free, but it could also be eliminated. -j > On May 31, 2018, at 7:12 PM, Torsten Curdt <tcu...@vafer.org> wrote: > > I need to get some random rows from a large(ish) table. > > The following seems to be the most straight forward - but not the fastest. > > SELECT * FROM table ORDER BY random() limit 200 > > Is there a faster/better approach? > > cheers, > Torsten > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
