SQLAlchemy: Return a record filtered by max value of a column

Question

All, I'm having an issue with a SQLA request; the goal is to return a record with the maximum value in the counter column:

this works fine - returns proper record:

m=12 # arbitrary example of a max value of counter = 12
qry = session.query(Data).
      filter(Data.user_id == user_id,Data.counter == m)

The code below does not work- returns None:

from sqlalchemy import func
qry = session.query(Data).
      filter(Data.user_id == user_id,
      Data.counter == func.max(Data.counter).select())

Note that there's never more than one record with a maximum value (if that's relevant).

Surely there's a way to return a record having the max value in one of the columns. Any Ideas?

Answer 1

It sounds like you could simply sort by counter in descending order and take the first result...

from sqlalchemy import desc

qry = session.query(Data).filter(
      Data.user_id == user_id).order_by(
      desc(Data.counter).limit(1)

However if you're concerned about sorting a large dataset, you could use a subquery...

subqry = session.query(func.max(Data.counter)).filter(Data.user_id == user_id)
qry = session.query(Data).filter(Data.user_id == user_id, Data.counter == subqry)

The sql that qry would essentially be...

SELECT * 
FROM data 
WHERE data.user_id = :user_id AND data.counter = (
    SELECT max(data.counter) AS max_1 
    FROM data 
    WHERE data.user_id = :user_id GROUP BY data.user_id
);