CODEWITHSUNDEEP
pythonmysqlsqlalchemy
Andrée B
Andrée B

Reputation: 127

SQLAlchemy Max value on column with Null value.

I have a list of value. I am trying to find the max of each ID. Where Null is max of all value. 

ID   DATE     

1     '2013-01-26'
1     NULL
1     '2013-03-03'      
2     '2013-02-23'      
2     '2013-04-12'      
2     '2013-05-02'

I want to get this

ID   DATE     

1     NULL    
2     '2013-05-02'

I know how to do it in mysql. By doing this 

select ID, max(IFNULL(DATE,'3000-01-01'))
from test
group by ID

I want to find the sqlalchemy version of this.

This does assume Null to be the min.

db_session.query(test, func.max(test.DATE)).group_by(test.ID).all()

Upvotes: 2

Views: 1767

Answers (1)

Slava Bacherikov
Slava Bacherikov

Reputation: 2066

This is pretty simple:

from datetime import date


# your query
res = session.query(
    Test,
    func.max(func.ifnull(Test.date, date(3000, 1, 1)))
).group_by(Test.id).all()

Note: you may use also COALESCE for this. COALESCE is part of standard sql, and it is supported by wider amount of databases. Example of query with COALESCE:

res = session.query(
    Test,
    func.max(func.coalesce(Test.date, date(3000, 1, 1)))
).group_by(Test.id).all()

Upvotes: 3

Related Questions