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
