CODEWITHSUNDEEP
pythonsqlalchemygreatest-n-per-group
Steven G
Steven G

Reputation: 17122

SqlAlchemy group_by and return max date

I have a table such has

identifier date        value
A          2017-01-01  2 
A          2017-01-02  1
A          2017-01-03  7
B          2017-01-01  2 
B          2017-01-02  7
B          2017-01-03  3

I am trying to select the max date of each identifier such as I get :

identifier date        value
A          2017-01-03  7
B          2017-01-03  3

thank you

Upvotes: 23

Views: 32820

Answers (4)

Amit Pathak
Amit Pathak

Reputation: 1367

In SQLAlchemy core, it can be achieved using the following code -

import sqlalchemy as db

query = db.select([
    TABLE.c.identifier,
    db.func.max(USERS.c.date),
    TABLE.c.value,
]).group_by(TABLE.c.identifier)

result = engine.execute(query).fetchall()

Upvotes: 2

r-m-n
r-m-n

Reputation: 15090

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
    )
)

Upvotes: 48

Philipp Graf
Philipp Graf

Reputation: 11

in orm you may write it almost as you would in mysql

result = session.query(Table,func.max(Table.date)).group_by(Table.identifier)
for row,i in result:
    print(row.date,row.value,row.identifier,i)

Upvotes: 0

Eugene Lopatkin
Eugene Lopatkin

Reputation: 2737

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.

Upvotes: 13

Related Questions