Sybase compatibility with TOP .. START AT

[Lukas Eder]

Hello,

I've noticed that the H2 SELECT statement supports a TOP clause, which is equivalent to using the H2 / HSQLDB / MySQL / Postgres / SQLite LIMIT clause. While Sybase ASE and SQL Server only know the TOP clause (without any offsets), Sybase SQL Anywhere also knows an optional TOP .. START AT .. clause, similar to LIMIT .. OFFSET ..

Some details can be seen on this Sybase blog:
http://iablog.sybase.com/paulley/2009/04/select-top-and-start-at/

Maybe this would be interesting for H2 to support as well?

Cheers
Lukas

[Thomas Mueller]

Hi,

I will add a feature request in the roadmap, however I wonder if it's really important if no other database supports it, not even Sybase ASE.

Regards,

Thomas

[Lukas Eder]

Hi Thomas,

You're probably right about the low priority. Seeing that Sybase (SQL Anywhere) thinks about adding support for the MySQL / Postgres LIMIT .. OFFSET clause anyway... and I have just now checked what SQL Server 2012 is going to be up to. Apparently, they're moving away all together from the TOP clause and implement something entirely different:

OFFSET 10 ROWS

FETCH  { FIRST | NEXT } 10 ROWS ONLY

http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx 

That will then precisely match DB2, Derby and Ingres syntax. Compatibility with that clause seems to be covered, already:

http://h2database.com/html/features.html?highlight=compatibility&search=compatibility#compatibility

Cheers

Lukas