I have an ORM setup with a "departments_milestones" table ("dm", DepartmentMilestone objects), with "department_id" and "seqOrder" Fields. Each department has a few records in this table, ordered within the department by "seqOrder" (so seqOrder is not unique).
Another object "ShotsStatus" contains a collection of these DepartmentMilestone objects, one for each department. I'm interested in getting the ShotsStatus records that are at the "last" milestone in its department - the one with the highest "seqOrder" value for the department. In SQL I can do something like this, with a subquery to select the departmental milestone records, reverse sort them so the 'last' one is first, and LIMIT 1 to get it, which is compared to the outer record. select * FROM shots_status ss left outer join otherschema.departments_milestones dm on (dm.department_id=ss.dept_id AND dm.milestone_id = ss.status_id) where ss.dept_id=723 and dm.id = (SELECT dmsq.id FROM plumber.departments_milestones dmsq WHERE dmsq.department_id=dm.department_id ORDER BY dmsq.seqOrder DESC LIMIT 1) I'd like to create a hybrid property "isLast" on the DepartmentMilestone object that returns True if it represents the 'last' milestone. I'm having trouble figuring out what sort of sqlalchemy select coding I'd need to recreate the subquery from the SQL code to do this. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.