CODEWITHSUNDEEP
pythonsqlalchemy
Bebop_
Bebop_

Reputation: 657

SQLAlchemy: return multiple values from sorting multiple columns

I have a postgres db holding data structured like this:

App-Type | App-Version | Version-Number | Description | Number

---------|-------------|----------------|------------ |---------

 Android |     Bulls   |      1.0       |    APK      |  100

 iOS     |     Bulls   |      1.0       |    plist    |  100

 Android |     Bulls   |      1.0       |    APK      |   99

 Android |     Titans  |      1.0       |    APK      |  100

 iOS     |     Titans  |      1.0       |    plist    |  100

 iOS     |     Titans  |      1.0       |    plist    |   99

 Android |     Titans  |      1.0       |     APK     |   98

I need to write a sqlalchemy expression to take return the highest number for each App-Type and also for each App-Version, so the data returned should look something like this. 

App-Type | App-Version | Version-Number | Description | Number

---------|-------------|----------------|------------ |---------

 Android |     Bulls   |      1.0       |    APK      |  100

 iOS     |     Bulls   |      1.0       |    plist    |  100

 Android |     Titans  |      1.0       |    APK      |  100

 iOS     |     Titans  |      1.0       |    plist    |  100

I'm having a lot of trouble trying to figure out how to return only the highest number per app-type for every version in app-version. Any help would be greatly appreciated here.

The query I'm working off of so far is 

sub_query = Table.query.order_by(desc(Table.app_version), Table.app_type, desc(Table.number))
query = sub_query.group_by(Table.app_version).group_by(Table.id).distinct(Table.app_version).all()

Upvotes: 2

Views: 1812

Answers (1)

van
van

Reputation: 76992

The most simple way is to first find highest number per appType/appVersion (using a subquery), and then join the main query on these results. 

VI = VersionInfo  # an alias to the mapped object

# subquery
sq = (
    session
    .query(
        VI.app_type.label("app_type"),
        VI.app_version.label("app_version"),
        func.max(VI.number).label("max_number"),
    )
    .group_by(VI.app_type, VI.app_version)
).subquery("subq")

# main query
q = (
    session
    .query(VI)
    .join(sq,
          and_(
              VI.app_type == sq.c.app_type,
              VI.app_version == sq.c.app_version,
              VI.number == sq.c.max_number,
          ))
)

Here one has to assume that there is only one highest number per appType/AppVersion combination

Upvotes: 3

Related Questions