sqlalchemy - grouping items and iterating over the sub-lists

Question

Consider a table like this:

| Name       | Version | Other |
| ---------------------|-------|
| Foo        | 1       | 'a'   |
| Foo        | 2       | 'b'   |
| Bar        | 5       | 'c'   |
| Baz        | 3       | 'd'   |
| Baz        | 4       | 'e'   |
| Baz        | 5       | 'f'   |
--------------------------------

I would like to write a sqlalchemy query statement to list all items (as mapper objects, not just the Name column) with max version: Foo-2-b, Bar-5-c, Baz-5-f. I understand that I would have to use the group_by method, but beyond that I am puzzled as to how to retrieve the sub-lists (and then find the max element). SQLAlchemy documentation is apparently not very clear on this.

In the real scenario, there are many other columns (like 'Other') - which is why I need the actual row object (mapper class) to be returned rather than just the 'Name' value.

Answer 1

Here's the SQL you can call with the engine.execute command:

select
    t1.*
from
    table t1
    inner join 
        (select
            Name,
            max(version) as Version
         from
            table
         group by
            name) s on
        s.name = t1.name
        and s.version = t1.version

Answer 2

If you need full objects you'll need to select maximum versions by name in a subquery and join to that:

max_versions = session.query(Cls.name, func.max(Cls.version).label('max_version'))\
                      .group_by(Cls.name).subquery()
objs = session.query(Cls).join((max_versions,
           and_(Cls.name == max_versions.c.name,
                Cls.version == max_versions.c.max_version)
       )).all()

This will result in something like this:

SELECT tbl.id AS tbl_id, tbl.name AS tbl_name, tbl.version AS tbl_version
FROM tbl JOIN (SELECT tbl.name AS name, max(tbl.version) AS max_version
FROM tbl GROUP BY tbl.name) AS anon_1 ON tbl.name = anon_1.name AND tbl.version = anon_1.max_version

Be aware that you'll get multiple rows with the same name if there are multiple rows with the max version.