Rick Morrision wrote:
BTW, OFFSET is pretty simple for MSSQL - no such thing, right now I'm throwing an exception if select.offset is not None. I've seen some rather ugly tricks to simulate OFFSET using NOT IN subqueries, but I'm not planning on going that direction (unless someone out there has a slick trick to share).
I'm not sure if there's a good generic way to simulate OFFSET in MSSQL. In my experience it usually requires a tweaked combination of TOP, WHERE, and ORDER BY to get what I want. Another option is using temporary tables, but again the solution seems to vary depending on the situation. However, I think raising an exception seems a bit mean. My vote would be to SELECT TOP (limit + offset), which returns more records than needed. Then (in Python) slice the LIMIT number of records off the end of the result set and return that to the user. The documentation for the MSSQL engine should note that using large offset numbers will result in inefficient queries due to the lack of an OFFSET feature. But at least this will shield the user from having to write engine-specific queries in most cases. The documentation should also note that OFFSET won't work on a nested subquery since the offset portion of the query is happening in Python. Note that SQL Server 2005 has ROW_NUMBER() OVER(ORDER BY ...) More here: http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx ~ Daniel ------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Do you grep through log files for problems? Stop! Download the new AJAX search engine that makes searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642 _______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
