I recently learnt that Ingres 9.2 has introducted the facility to restrict the rows returned from a query by specifying an offset.

Where previously you would only be able to select the first n rows:
SELECT FIRST n col_name FROM table WHERE col_name = 'value';

There is now an "OFFSET" clause:
SELECT FIRST n col_name FROM table WHERE col_name = 'value' OFFSET m;

I went about implementing this in OpenJPA, but found that this dual-location for the Select Range specification was going to cause trouble, so I'd have to override a core method. The problem with that was that the method in question was marked "private", so an override wouldn't have the desired effect! I posted on the developer mailing list about this and they had no problem to me opening up the access, but then I heard about another syntax:

SELECT col_name FROM table WHERE col_name = 'value' OFFSET m FETCH FIRST n ROWS ONLY;

(FIRST can also be replaced by the keyword "NEXT")

This solved my problems, as I was able to specify the range at the end of the query; OpenJPA had a constant for specifying this position (DBDictionary.RANGE_POST_SELECT).

I had a quick play with this new syntax, wondering what
SELECT FIRST x col_name FROM table OFFSET y FETCH NEXT z ROWS ONLY;
would do. Fortunately Ingres prevented me from mixing the FIRST and FETCH FIRST z tokens in a single statement!

Obviously, when restricting the number of rows selected it is important to have a guaranteed ordering, so an ORDER BY clause should also be used. The ORDER BY clause comes before the OFFSET/FETCH FIRST clause.

There's always loads of new features being added to Ingres; these were added in the 9.2 release. For a summary of the new features in a given release, see the Release Summary document; this is currently available for Ingres 9.2 at [url]docs.ingres.com[/url].