Re: [rfc] Row limit support for Pear DB

Graeme Merrall wrote: > > Quoting Morgan Christiansson <morgan.christiansson@telia.com>: > > > I THINK that oracle has something like this: > > > > SELECT * WHERE rownum BETWEEN ($from,$to) > > > > i'm not sure if oracle supports the BETWEEN syntax though but otherwise > > > > it would be > > > > SELECT * WHERE rownum <= $from AND rownum >= $to > > > > Although it was more than a year ago i used oracle, and i didn't use it > > > > very much. :) > > > > I've been in a discussion on row sets in oracle as well as someone came up with > this one as well. The main problem was ordering because ROWNUM has precedence > over ORDER BY. > I've not tried the solution below using MINUS but I know I've used the > subselect method plenty of times to emulate LIMIT. > > select * from > (select row1, row2,... from table_name order by row1) > where rowno < 31 > MINUS > select * from > (select row1, row2,... from table_name order by row1) > where rowno < 20 I'm afraid this would be too slow. Anyway, I don't think this works with arbitrary queries because there may be problemas computed columns like count(), sum(), etc... This is a very old problem to me. If it was easy, I would have already supported it in Metabase. I know there may be a solution using server side cursor extensions, but I did not have the time to investigate. When I will do it, I will add that support in Metabase. Meanwhile, Metabase supports limited queries with Oracle simply by skipping any unwanted rows. Regards, Manuel Lemos