incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From McKinley <mckinley1...@gmail.com>
Subject Re: Rowlimits: OFFSET and LIMIT
Date Tue, 19 Jan 2010 16:43:17 GMT
On Tue, Jan 19, 2010 at 3:56 PM, Jaco van Tonder <Jacovt@tebabank.com> wrote:
> McKinley: Here is what I found. Seems like Oracle and MSSQL is pretty close to each other
with regards to the window functions (OVER...). http://www.troels.arvin.dk/db/rdbms/#select-limit-offset

Thanks for the links. Very thorough. However, I did not see the
partition clause mentioned which I know exists for SQL Server. Does it
exist for Oracle?

> MSSQL uses the TOP syntax:
> http://viewvc.jboss.org/cgi-bin/viewvc.cgi/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/SQLServerDialect.java?view=markup

Could MSSQL use the windowing function only and not TOP? It seems like
to me it can. Perhaps they are using TOP to support SQL Server 2000.

> Wondering how difficult it would be to "port" that functionality?

It will not be difficult in the slightest. However, ROW_NUMBER OVER()
works very different from LIMIT and OFFSET in that ROW_NUMBER OVER()
requires that it be a sub query to be limited.  I think limit and
offset would be the first DBCommand level methods that would so
radically alter a query. Because ROW_NUMBER OVER() is already not
portable, perhaps the limit and offset should not automatically sub
query the original DBCommand. It would probably be best to make the
API user only apply limit and offset to sub queries. The DBMS can
catch their error.

For now my patch will require you to use ROW_NUMBER OVER() with
BETWEEN, >, < etc. I will not abstract it into limit and offset yet.

Thanks,

McKinley

Mime
View raw message