db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Paging Result Sets
Date Mon, 28 Apr 2008 15:02:47 GMT
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,

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

View raw message