On May 15, 2008, at 9:02 AM, Matt Chambers wrote:
Just googling around I would say that the LIMIT/OFFSET feature has to be the #1 requested Derby feature of all time.
Paging is such a necessary part of web development, and MySQL, despite its warts, really gets it right. It is a hassle to have to jump through hoops (subselects, temp tables, inverted sort orders, ...) to do it. It is a shame because apparently the "standard" has no provision for this. But when you're putting data on the web, you simply must page records. And with MySQL it is a snap to augment any query to add paging, and it is fast.
So I am not surprised this is a major FAQ. I have the same problem with MSSQL as well. But my vague memory is that I can put ROW_NUMBER in a FROM clause in Sybase, and then use it in my WHERE, making it easy there as well.
We're pretty much back to square 1 on paging in Derby because we need to sort *before* we page (obviously). So now we're trying temporary tables (nuisance because our system uses configurable queries) and just spinning through the result set in Java.
Geoff
