SqlAlchemy select with max, group_by and order_by

Question

I have to list the last modified resources for each group, for that I can do this query:

model.Session.query(
    model.Resource, func.max(model.Resource.last_modified)
).group_by(model.Resource.resource_group_id).order_by(
    model.Resource.last_modified.desc())

But SqlAlchemy complains with:

ProgrammingError: (ProgrammingError) column "resource.id" must appear in
the GROUP BY clause or be used in an aggregate function

How I can select only resource_group_id and last_modified columns?

In SQL what I want is this:

SELECT resource_group_id, max(last_modified) AS max_1
FROM resource GROUP BY resource_group_id ORDER BY max_1 DESC

Answer 1

model.Session.query(
    model.Resource.resource_group_id, func.max(model.Resource.last_modified)
).group_by(model.Resource.resource_group_id).order_by(
    func.max(model.Resource.last_modified).desc())