On Tue, Jan 19, 2010 at 3:56 PM, Jaco van Tonder <[email protected]> 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
