There are several ways to approach this.
You can run the aggregation once in a common table expression and use that in a sub-query.
with totals as (
SELECT Day, Flight, SUM(LuggageWeight) total_weight
FROM test
GROUP BY Day, Flight
)
select *
from totals
where total_weight = (select max(total_weight) from totals);
Or combine the grouping with window functions:
select day, flight, total_weight
from (
SELECT Day, Flight,
SUM(LuggageWeight) total_weight,
dense_rank() over (order by SUM(LuggageWeight) desc) as rnk
FROM test
GROUP BY Day, Flight
) totals
where rnk = 1;
The above can easily be extended to also include the 2nd heaviest flight and so on. The derived table (sub-query) totals
is essentially only necessary because of visibility rules.
And if you are only interested in a single row, even if there is more than one day/flight combinations with the same (highest) total weight, you can use:
SELECT Day, Flight, SUM(LuggageWeight) total_weight
FROM test
GROUP BY Day, Flight
order by SUM(LuggageWeight) desc
fetch first 1 rows only;
Again: the above is not exactly the same as the other solutions, but might be good enough.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…