Hi, I would avoid using "order by rand()" if possible.
The trick with min(id) / max(id) should work, unless there are many gaps. If there are many gaps, things get complicated. I don't know if there is a good way that works well in all databases. Maybe you want to ask on StackOverflow? An efficient algorithm would be to use random access / index lookup, or skip (to select the nth row of a table). But like most databases, H2 doesn't support it yet. The next-generation storage engine for H2, the MVStore, does in fact support this feature. But the MVStore isn't stable yet, and running SQL against the MVStore isn't supported at this time: http://h2database.com/html/mvstore.html "Also supported, and very uncommon for maps, is fast index lookup." Regards, Thomas On Mon, Jan 14, 2013 at 11:27 AM, Noel Grandin <[email protected]>wrote: > > On 2013-01-14 12:08, Luigi R. Viggiano wrote: > > > I've searched around and I found answers like this: SELECT * FROM sample > ORDER BY RAND() limit X. > > It looks to me that this will fetch the full table, isn't it? > > Yeah, that wouldn't work very well with H2, we'd scan the whole table. > > I think it would be best to generate 10 or 50 random intengers and do a > "select * from sample where rowid in (<random integer list>)". But afaik, > rowid concept is missing in H2, so I may opt for using the ID column in my > table. > > > Fetch MIN(id) and MAX(id) and cache them, then generate some random > numbers between the two and do a > > select * from sample where id in (<random integer list>) > > That should do the trick since we tend to be very linear with our > sequences. > > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
