As far as I can tell, Derby does not currently support the SQL Limit command. Am I missing something? If it doesnt, are there any plans to implement it?
LIMIT is really a MySQL feature not an SQL command. Different databases implement this in different ways as it was not standardized until SQL03 (with window functions that I don't think anyone implements yet and which are really there for OLAP processing).
One problem with it is that SQL does not define the order in which rows are returned; even order by will not define the order for rows with the same sort key. This means that if you execute the same query twice you may get the rows returned in different orders - this is common on any database that uses a parallel query algorithm. As a result the data returned by LIMIT/OFFSET may well be non-deterministic.
Subject to these constraints, you can mimic this on the client side by controlling how you iterate over the ResultSet - e.g. skip offset rows, process limit rows and then close it. This actually gives you a solution that is portable between database vendors.
The downside is that the offset rows need to be evaluated and returned to the client only to be discarded. However, unless offset is very large (which would imply that perhaps you should look for a better query strategy anyway) this is probably not where the majority of your processing effort is going. Further, if you are using Derby embedded then you are simply duplicating what the server would need to do to implement offset anyway so the difference is going to be minimal.
However, if you feel that LIMIT/OFFSET is critical or useful functionality you might want to open a Jira item to add it as an enhancement.
-- Jeremy
