distinct (or group by) with max in sqlalchemy - how to?

[Nguyen, Thang P]

Hi,

I've recently started learning sqlalchemy. So far, I am able to do everything I needed with sqlalchemy query. Now, I am getting into a situation, and hope someone would give me a suggestion. Below is my question.

My test table has two columns Name and Count

Name Count

A 0
A 1
A 2
A 3
A 4
B 0
B 1
B 2
C 0
C 1

What I want from a query is basically the distinct name and the max count associated with that name, as shown below:

Name Count
A 4
B 2
C 1

What would be best way to do this? Any suggestion is appreciated.

thanks
Thang

[Mike Conley]

See the documentation at

http://www.sqlalchemy.org/docs/05/sqlexpression.html#functions

Something like this

Using ORM mapped classes

session.query(Tabl.name, func.max(Tabl.cnt)).group_by(Tabl.name).all()

or SQL expression language

select([tabl.c.name,func.max(tabl.c.cnt)]).group_by(tabl.c.name).fetchall()