Search code examples
sqlitegroup-bysqlalchemy

SQLAlchemy/SQL, how to get value in one column based on another column within a group

I have this simple table in a sqlite database:

unit    groupid value
enhed1  24270   2
enhed2  24272   2
enhed3  24120   1
enhed4  24191   4
enhed4  24192   2

For each unit, I would like to extract the value where groupid (within group) is the lowest.

unit    value
enhed1  2
enhed2  2
enhed3  1
enhed4  4

What I have tried:

select(
    [
        table.c.unit,
        table.c.value
    ]
)
.where(
    table.c.groupid == select(func.min(table.c.groupid  )).group_by(table.c.unit).scalar_subquery()
)
.group_by(table.c.unit)

But it doesn't work, and just outputs the same value for all units.

Solution

  • you don't need any filtering just group_by and order:

    select(
    [
        table.c.unit,
        table.c.value
    ]
) \
.group_by(table.c.unit) \
.order_by(table.c.unit) \
.scalar_subquery()