Derby 10.5 preview: FETCH/OFFSET

One of the most frequently requested features for Derby is the LIMIT/OFFSET syntax supported by many other databases. That syntax allows you to skip the first N rows from the result and return the next M rows. In the upcoming 10.5 release, it will finally be available to Derby's users too, except that it's called FETCH/OFFSET and uses the somewhat more verbose syntax dictated by the SQL:2008 standard.

The new clauses can be added to any SELECT statement, and they're defined like this:

[ OFFSET integer-literal {ROW | ROWS} ]
[ FETCH { FIRST | NEXT } [integer-literal] {ROW | ROWS} ONLY ]

(No, I'm not kidding, the standard actually requires you to type all those seemingly redundant keywords...)

You can use FETCH and OFFSET both alone and in combination with each other. For example, to select the three persons with the highest score from a table, you'd use a FETCH clause and no OFFSET clause, like this:

ij> SELECT NAME, SCORE FROM RESULTS ORDER BY SCORE DESC
> FETCH FIRST 3 ROWS ONLY;
NAME      |SCORE      
----------------------
John      |33         
Anne      |28         
Sue       |21         

3 rows selected

If you want to select all persons except the two with the highest score, you could use an OFFSET clause and skip the FETCH clause:

ij> SELECT NAME, SCORE FROM RESULTS ORDER BY SCORE DESC OFFSET 2 ROWS;
NAME      |SCORE      
----------------------
Sue       |21         
Peter     |19         
Bob       |7          

3 rows selected

And if you're only interested in the fourth person, you could use a SELECT statement that combines OFFSET and FETCH like this:

ij> SELECT NAME, SCORE FROM RESULTS ORDER BY SCORE DESC
> OFFSET 3 ROWS FETCH NEXT ROW ONLY;
NAME      |SCORE      
----------------------
Peter     |19         

1 row selected

The keywords FIRST and NEXT are synonyms, and so are ROW and ROWS, so all of these these four clauses are allowed and have the same meaning:

FETCH FIRST 10 ROWS ONLY
FETCH NEXT 10 ROWS ONLY
FETCH FIRST 10 ROW ONLY
FETCH NEXT 10 ROW ONLY

SQL:2008 also allows you to have ORDER BY/OFFSET/FETCH clauses in subqueries, but Derby doesn't implement that yet, so you'll be limited to using them on the top level of your query for now.

If you're interested in helping out testing the Derby 10.5 release candidate, more information (including download links) can be found here.

Comments:

Thanks for LIMIT feature,I have been waiting long for it!. I do not however understand why you are not supporting LIMIT as a special case of FETCH.

Posted by neel on April 22, 2009 at 05:39 PM CEST #

Hi Neel,

It should be easy to make Derby support LIMIT now that it supports FETCH/OFFSET. The problem is that LIMIT is not part of the SQL standard, and the exact syntax is not the same across the different databases that support LIMIT, so it's not clear which syntax to implement. As long as there is a defined standard for it, it's probably better to stick to the standard.

Posted by Knut Anders Hatlen on April 30, 2009 at 05:59 AM CEST #

Thanks. I am not familiar with the sql standard. Your approach seems very reasonable.

Posted by neelakanth Nadgir on April 30, 2009 at 12:58 PM CEST #

Post a Comment:
Comments are closed for this entry.
About

kah

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today