Howto: Difficult self referencing queries

[Dani Hodovic]

I'm wondering how the following two queries could be elegantly composed in SqlAlchemy.

SELECT t1.* FROM mytable t1 LEFT OUTER JOIN mytable t2 ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date") WHERE t2.UserId IS NULL;

and

select userid, value from users u1 where date = (select max(date) from users u2 where u1.userid = u2.userid)

The queries can be found here: http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column/121435#121435

I think I've figured out query 1, but I'm not sure if my solution is optimal (can post if needed, don't want to make a wall of text). In general how do you self reference a table in a subquery as in example 2?

Thanks

[Mike Bayer]

the query you have there is a scalar subquery.

you'd be combining the use of correlation as you can see in http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#correlated-subqueries, that's Core, for ORM call as_scalar() on the Query, and since it's the same table twice you'd need to use aliases as found in aliases.

The bug tracker here is not for usage questions. see the mailing list guidelines.