I'm looking at adding limit and offset to the HQL grammar to support
things like grabbing the top row sorted by date in a subquery. This
is related to the most popular Linq "bug." Other than the limitations
of particular databases, I'm not imagining there will be a great
number of problems implementing this.
The biggest issue I have right now is deciding the syntax. SQL 2008
specifies the following syntax for the limit clause that appears after
the order by clause.
Option A (ANSI SQL)
[ OFFSET integer-literal {ROW | ROWS} ]
[ FETCH { FIRST | NEXT } [integer-literal] {ROW | ROWS} ONLY ]
This is the standard, but quite verbose. Specifying first, next, row,
rows, or only has absolutely no effect, but is required by the
standard.
Option B (PostgreSQL, MySQL, etc.)
[LIMIT integer]
[OFFSET integer]
I'm not even going to propose using something like row_number as a hql syntax.
What are people's thoughts on this?
Patrick Earl
