Adding ORDER BY gives error "must appear in the group by clause or be used in an aggregate function"

Question

I want to add an ORDER_BY on car_max_price in my query but don't want it in the GROUP_BY.

How to fix the must appear in the group by clause or be used in an aggregate function error? Any idea of what's wrong?

 subquery = (
                session.query(
                    garage.id,
                    garage.name,
                    func.max(func.coalesce(car.max_price, 0)).label("car_max_price"),
                    func.jsonb_build_object(
                        text("'type'"),
                        car.type,
                        text("'color'"),
                        car.color
                        text("'max_price'"),
                        func.max(car.max_price),
                    ).label("some_cars"),
                )
                .group_by(
                    garage,
                    car.type,
                )
                .subquery("subquery")
            )
    
            query = (
                session.query(
                    func.jsonb_build_object(
                        text("'id'"),
                        subquery.c.id,
                        text("'name'"),
                        subquery.c.name,
                        text("'some_cars'"),
                        func.jsonb_agg(
                            func.distinct(subquery.c.some_cars),
                        ).label("some_cars"),
                )
                .select_from(subquery)
                .group_by(
                    subquery.c.id,
                    subquery.c.name,
                )
                .order_by(
                    subquery.c.car_max_price
                )
            )
            return query

Answer 1

When aggregating, you cannot order by an un-aggregated column. There may be any number of different values in the same aggregated group of rows for it. So you have to define what to use exactly (by way of another aggregate function).

Order by min(subquery.c.car_max_price) or max(subquery.c.car_max_price) or avg(subquery.c.car_max_price) or whatever you actually need.

There is one exception to this rule: if the PRIMARY KEY of a table is listed in the GROUP BY clause, that covers all columns of that table. See:

• PostgreSQL - GROUP BY clause

But while operating on a derived table (subquery) that exception cannot apply. A derived table cannot have a PK constraint.