SQLAlchemy: Returns record filtered by maximum column value
That's it, I'm having a problem with a (surprisingly trivial) SQLA query; The goal is to return the record with the highest value in the counter column:
this works great - returns the correct entry:
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 doesn't work - it returns None
:
from sqlalchemy import func
qry = session.query(Data).
filter(Data.user_id == user_id,
Data.counter == func.max(Data.counter).select())
Please note that there is no more than one record with the maximum value (if applicable).
Of course, there is a way to return the record that has the maximum value in one of the columns. Any ideas?
source to share
It looks like you could just 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 are concerned about sorting a large dataset, you can 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)
sql, which 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
);
source to share