Search code examples
sqlderby

Derby DB SQL, Select Rows starting from row number

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?

Solution

  • 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