Option B, cos:
1. I can't think of a single RDBMS that actually implements the ANSI
standard (SQL Server team are you listening?);
2. The OFFSET x LIMIT y is the most readable (bay far), and is closest to
our internal implementation terminology;
3. I can't think of a single advantage to the more verbose option and it
looks like more work.
-----Original Message-----
From: Wenig, Stefan
Sent: Sunday, February 06, 2011 9:47 AM
To: [email protected]
Subject: AW: Re: [nhibernate-development] Limit and Offset support in HQL
Those 2 don't work for subqueries, that's why we need it in the grammar
AKAIR.
Sent from my HTC phone
----- Ursprüngliche Nachricht -----
Von: Richard Birkby <[email protected]>
Gesendet: Sonntag, 6. Februar 2011 08:32
An: [email protected]
<[email protected]>
Betreff: Re: [nhibernate-development] Limit and Offset support in HQL
Is this equivalent to adding Query.SetFirstResult(...) and
Query.SetMaxResults(...) into the HQL grammar?
Richard
On 6 Feb 2011, at 06:11, Patrick Earl <[email protected]> wrote:
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
