SqlAlchemy group_by and return max date
With ORM you could use over
function that is actually is a window function:
session \
.query(Table, func.max(Table.date)
.over(partition_by=Table.identifier, order_by=Table.value))
It returns a tuple (table_instance, latest_datetime). order_by
is optional in this case.
The same with SQL Expressions.
Using a subquery:
SELECT t1.identifier, t1.date, t1.value FROM table t1
JOIN
(
SELECT identifier, MAX(date) maxdate
FROM table
GROUP BY identifier
) t2
ON t1.identifier = t2.identifier AND t1.date = t2.maxdate;
In SQLAlchemy:
from sqlalchemy import func, and_
subq = session.query(
Table.identifier,
func.max(Table.date).label('maxdate')
).group_by(Table.identifier).subquery('t2')
query = session.query(Table).join(
subq,
and_(
Table.identifier == subq.c.identifier,
Table.date == subq.c.maxdate
)
)