How to retrieve data from HSQL within a row range?

Question

I have worked with oracle where to select data within a particular row range the query is :

SELECT * from TABLE WHERE rownum>0 AND rownum<=10

What would be the corresponding query in HSQL?

Answer 1

HSQLDB supports the LIMIT and OFFSET keyword

select *
from some_table
limit 10
offset 2;

Note that limit and offset make no sense without an order by clause (which is true for Oracle as well).

HSQLDB also supports the ANSI SQL standard fetch first x rows but that will require the use of an order by

select *
from some_table
order by some_column
offset 2 rows
fetch first 10 rows only;

The above is also supported by Oracle since 12.1

See the manual for details
http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_slicing