There was some discussion about doing limit/offset on Oracle, to which I
posted a really ugly piece of SQL to handle this.  It appears that
SQLAlchemy has found a slightly cleaner way to handle this with the
ROW_NUMBER function.

So, a query like this:
  SELECT id, name FROM my_table ORDER BY id LIMIT 10 OFFSET 20

Would become:
  SELECT id, name FROM
  (SELECT id, name, ROW_NUMBER() OVER (ORDER BY id) AS ora_row_num
   FROM my_table ORDER BY id)
  WHERE ora_row_num > 10 AND ora_row_num <= 30

This eliminates one level of subselects from the approach I previously
posted.  The "OVER (ORDER BY..." is required for ROW_NUMBER, so if the
query didn't specify an ordering, then a default such as the primary key
would need to be added.

Hopefully this approach would make the limit/offset a little more
reasonable to add to the Oracle backend.  The SQLAlchemy implementation
is here:
http://sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/sqlalchemy/databases/oracle.py?rev=1558#L278

-- 
Matt Good <[EMAIL PROTECTED]>



-------------------------------------------------------
All the advantages of Linux Managed Hosting--Without the Cost and Risk!
Fully trained technicians. The highest number of Red Hat certifications in
the hosting industry. Fanatical Support. Click to learn more
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=107521&bid=248729&dat=121642
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Reply via email to