Derby DB SQL, Select Rows starting from row number

Question

How can I select from row X to row Y in a SQL query in Derby DB?

For example:

• I would like to select rows 15 - 30, but not top 15.
• Select all the rows starting from row number 30.

I tried LIMIT and ROWNUM but they do not work. How can I do it in Derby?

Answer 1

According to the FAQ:

Derby does not support the LIMIT syntax. However, Derby 10.4 added the ROW_NUMBER function and Derby 10.7 added the OFFSET and FETCH clauses.

Derby also supports limiting the number of rows returned by a query through JDBC.
<...>

Starting with the 10.4.1.3 release Derby also supports limiting the number of rows using the ROW_NUMBER function.
<...>

The ROW_NUMBER function can also be used to select a limited number of rows starting with an offset, for example:
<...>

SELECT * FROM ( 
    SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.* 
    FROM myLargeTable 
) AS tmp 
WHERE rownum > 200000 AND rownum <= 200005; 

---

If you are using Derby 10.7 or newer you can, also, use the OFFSET and FETCH clauses:

SELECT * FROM T ORDER BY I 
    OFFSET 10 ROWS 
    FETCH NEXT 10 ROWS ONLY