db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Embretsen <John.Embret...@Sun.COM>
Subject Re: Paging Result Sets
Date Tue, 29 Apr 2008 06:42:56 GMT
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.


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

View raw message