sql - Query to see the MAX value in Oracle

Question

I have the following N:N table (which stores the people boardings) in my Oracle Database (it is about an airport):

CREATE TABLE boardings(
Passport VARCHAR2(8),
Day DATE,
Flight VARCHAR2(8),
LuggageWeight NUMBER(4,2),
CONSTRAINT pk PRIMARY KEY(Passport, Day, Flight));

And I would like to make a query in order to see for each flight, which has been the day that has transported the highest amount of weight (keep in mind that a same flight, as RY-1234-VY for example, can make different travels in different days. I have been trying something like this, but it doesn't work:

SELECT Day, Flight
FROM test
GROUP BY Day, Flight
HAVING SUM(LuggageWeight) = (SELECT MAX(SUM(LuggageWeight))
                             FROM test
                             GROUP BY Day, Flight);

Answer 1

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.