CODEWITHSUNDEEP
sqlalchemy
saccharine
saccharine

Reputation: 904

sqlalchemy SELECT column FROM (SELECT columns FROM TABLE)

I have the following query

reports = self.session.query((
        func.sum(Report.a) / func.sum(Report.b))
            .label('c'),
        Report.id,
        Report.id2
        ).group_by(Report.id, Report.id2
        )

I now want to get the max(c) for the reports, grouped by id.

Essentially, I am trying to have a sqlalchemy solution to this problem SQL Select only rows with Max Value on a Column

but with the extra requirement that I need to calculate the value column I want to have the max in as in Selecting max value from calculated column in mysql

I am finding it difficult to perform a 

SELECT MAX(C), id FROM (SELECT A/B AS C, id FROM TABLE) t

Upvotes: 2

Views: 3812

Answers (1)

Audrius Kažukauskas
Audrius Kažukauskas

Reputation: 13533

Mark your first query as subquery:

reports = session.query(
    (func.sum(Report.a) / func.sum(Report.b)).label('c'),
    Report.id,
    Report.id2
).group_by(Report.id, Report.id2).subquery()

After this it can be used in another query as if it was a table:

# reports.c is a shorthand for reports.columns
q = session.query(
    func.max(reports.c.c),
    reports.c.id
).group_by(reports.c.id)

Upvotes: 2

Related Questions