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?

See if [this](http://stackoverflow.com/questions/6612862/hsqldb-rownum-compatibility-with-oracle) works for you — Gurwinder Singh, Dec 19 '16 at 13:30
`rownum > 0` is useless in Oracle and if you intend to use e.g. `rownum > 5` then that [won't work](https://docs.oracle.com/database/121/SQLRF/pseudocolumns009.htm#SQLRF00255) in Oracle — , Dec 19 '16 at 13:30

score 8 · Accepted Answer · 2016-12-19T13:37:58.423

8

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

edited Dec 19 '16 at 13:37

answered Dec 19 '16 at 13:30

1 Answers1