Oracle Compatible Row Limiting in H2

2017 March 26

If you are concerned with H2 and Oracle compatibility in your SQL, e.g. because you’re using H2 for testing and Oracle for production, you’ll be glad to know that H2 1.4.177 and later support Oracle row limiting syntax (12c and later)

OFFSET x ROWS
FETCH FIRST n ROWS ONLY 

Unfortunately this is currently not documented but this is hopefully fixed in the future.

Know that if OFFSET is used for pagination it has correctness issues if the result set is updated between two queries and has performance issues for large offsets. For more information check out Markus Winand’s article on keyset pagination.