[Sqlalchemy-tickets] Issue #3311: Howto: Difficult self referencing queries (zzzeek/sqlalchemy)

[Dani H. <iss...@bi...>]

New issue 3311: Howto: Difficult self referencing queries
https://bitbucket.org/zzzeek/sqlalchemy/issue/3311/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