It's about time FAQ 5.2 gets updated, now that Derby has a more useful "workaround" for those looking for LIMIT. I'll see what I can do about that.
-- John Rick Hillegas wrote: > Hi Geoff, > > The new 10.4 release provides support for the sql standard ROW_NUMBER > function, which you can use for paging through query results. You can > find this function documented in the ROW_NUMBER subsection of the > "Built-in functions" section of the 10.4 Reference Manual. > > Hope this helps, > -Rick > > Six Fried Rice wrote: >> I think this is a FAQ (indeed, it is in the FAQ with a rather terse >> answer) but I'm looking for a little more guidance. >> >> I need to be able to "page" the results of queries in Derby. Since >> Derby does not have an equivalent of MySQL's LIMIT, MSSql's TOP, or >> Oracle's ROW_NUM, there doesn't seem to be a way to do this on the SQL >> side. The recommendation is to use JDBC. I found a few mailing list >> responses that suggest using JDBC conventions but don't go in to much >> detail. >> >> I'm assuming the solution is something along these lines: >> >> 1: Calculate the starting and ending offset desired. >> 2: call setMaxRows() on the statement to fetch up to the *ending* offset. >> 3: spin through the result set until the *starting* offset is reached. >> 4: process records until the end of the result set. >> >> Is this the right approach? Are there any best practices to ensure >> that step 3 is as fast as possible? >> >> Thanks! >> >> Geoff > -- John H. Embretsen Engineer (Quality Engineering) Sun Microsystems, Database Technology Group Haakon VII gt. 7b, N-7485 Trondheim, Norway Tel: x43447/+47 73842147 Cell: +47 97061091
