Hi Pertti,
Pertti Ylijukuri wrote:
I have understood that in derby doesn't support offset in sql.
Correct. Derby does not support offset.
To my knowledge offset is actually not part of neither the SQL99 nor
SQL2003 specs. Derby (attempts to) strictly adhere to these specs, and
this is one of the main reasons why it's not supported.
However in hibernate with derby this is possible using
Query.setFirstRow(int row). What it actually to do that?
Derby implements scrollable result sets, which is a requirement for
Hibernate to provide Query.setFirstRow().
Please refer to any online JDBC tutorial, and/or the derby docs, on how
to use scrollable resultsets.
Also, the upcoming derby 10.4 release includes the ROW_NUMBER() window
function which is SQLs standardized way of limiting the number of rows
returned and it's offset. Please see the following wikipage for a quick
introduction in case you are unfamiliar with ROW_NUMBER():
http://wiki.apache.org/db-derby/OLAPRowNumber
Hope this helps :)
Cheers,
Thomas
