Take the 2-minute tour ×

This should be simple, but I can't seem to figure it out.

Here's my table:

class UserEvent(Base):
    __tablename__ = 'user_events'

    user_id = Column(Integer, ForeignKey('users.user_id'),
                            primary_key=True, nullable=False)
    event_time = Column(DateTime, primary_key=True, nullable=False)
    detect_time = Column(DateTime, nullable=False)
    new_state = Column(Boolean, nullable=False)

And here's some example data:

| user_id | event_time          | detect_time         | new_state |
|       1 | 2012-11-12 16:12:00 | 2013-01-31 20:55:31 |         1 |
|       1 | 2012-11-12 18:24:00 | 2013-01-31 20:55:33 |         0 |

I want to find the newest (event_time) UserEvent for each user_id.

I've tried this:

for event, current in session.query(
        UserEvent, func.max(UserEvent.event_time)).group_by(

The query returns the correct "event" (2012-11-12 18:24:00). However, it is JOINED incorrect (or something) because "current" is True.

No matter how many rows are in the table, I always get back the most recent event_time and OLDEST new_state.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Are you using MySQL? That particular expression will generate query:


which is invalid on most databases, but gives you a random row with MySQL. You may find out more about the behavior from http://news.ycombinator.com/item?id=5122798 (coincidentally, written by SQLAlchemy's author)

This expression would work:

for user_id, current in session.query(
    UserEvent.user_id, func.max(UserEvent.event_time)).group_by(

though it returns user_id instead of instances of UserEvent.

Something like this would probably give what you want:

t = session.query(

query = session.query(
    UserEvent.user_id == t.c.user_id,
    UserEvent.event_time == t.c.max_time,
share|improve this answer
That works perfectly. Interestingly, I did come across an example similar to this before I posted. I just assumed that it would be possible to do it in one query instead of two, although I suppose it could still be counted as one because "t" is a subquery. Anyways, thanks for the correct answer. –  fandingo Feb 2 '13 at 17:58
Glad it works :) As far as I know, the only way to avoid subquery in such situation is by using postgresql's DISTINCT ON syntax (postgresql.org/docs/9.2/static/…), which is non-standard but really really convenient. –  sayap Feb 3 '13 at 1:03

Your Answer


