Take max correlated value in Oracle?

Question

I have the following tables in my Oracle 11g database:

CREATE TABLE travels
(
Day DATE,
Flight_Code VARCHAR2(7),
Airplane_Code VARCHAR2(7),
CONSTRAINT pk PRIMARY KEY(day,flight_code)
);

and

CREATE TABLE boardings
(
Passport VARCHAR2(7),
Day DATE,
Flight_Code VARCHAR2(7),
Luggage_Weight NUMBER(4,2),
CONSTRAINT pk PRIMARY KEY(passport,day,flight_code)
);

So I am trying to make a query in order to see for each airplane which has been the flight (Day and Flight_Code) that it has transported the maximum amount of weight, being that amount always higher than 100 (keep in mind that a same flight, as RY-1234-VY for example, can make different travels in different days, but not more than one in a same day).

I have been trying something like this but it doesn't work, because it returns for each flight which has been the day that it has been transported more Luggage_Weight, and the airplane who has done it.

SELECT Airplane_Code, Day, Flight_Code
FROM Travels
WHERE (Day, Flight_Code) IN (SELECT Day, Flight_Code
                                 FROM boardings b1
                                 GROUP BY Day, Flight_Code
                                 HAVING SUM(Luggage_Weight) = (SELECT MAX(SUM(Luggage_Weight))
                                                              FROM boardings b2
                                                              WHERE b1.Flight_Code = b2.Flight_Code
                                                              GROUP BY Day, Flight_Code
                                                              HAVING SUM(Luggage_Weight) > 100))
GROUP BY Airplane_Code, Day, Flight_Code;

I would like the solution to use GROUP BY and HAVING structure.

For example:

INSERT INTO travels VALUES ('04/04/2020', 'RY1234', 'ABCD');
INSERT INTO travels VALUES ('03/04/2020', 'RY1234', 'ABCD');

INSERT INTO boardings VALUES ('ES1234', '04/04/2020', 'RY1234', '51');
INSERT INTO boardings VALUES ('ES5678', '04/04/2020', 'RY1234', '50');
INSERT INTO boardings VALUES ('ES9101', '03/04/2020', 'RY1234', '100');

As u can see, the same flight (RY1234) has travelled in 2 different days, so in 03/04, the total LuggageWeight has been 100kg, but in the 04/04, the total LuggageWeight has been 101kg. The result should be the next:

ABCD ---- 04/04/2020 ---- RY1234

In the case that it wouldn't have reached the 100kg in any of its flights for the same airplane, that airplane code should not be shown.

Answer 1

You can use subquery refactoring along with GROUP BY and HAVING like below to get your result.

When you say the below you need to group by Flight_code alone to get the Max weight.

So I am trying to make a query in order to see for each airplane which has been the flight (Day and Flight_Code) that it has transported the maximum amount of weight, being that amount always higher than 100 (keep in mind that a same flight, as RY-1234-VY for example, can make different travels in different days, but not more than one in a same day).

            WITH t
             AS (SELECT t.airplane_code,
                        t.day,
                        t.flight_code,
                        SUM(luggage_weight) wt
                 FROM   travels t,
                        boardings b
                 WHERE  t.flight_code = b.flight_code
                        AND Trunc(t.day) = Trunc(b.day)
                 GROUP  BY t.airplane_code,
                           t.day,
                           t.flight_code
                 HAVING SUM(luggage_weight) > 100),
             t1
             AS (SELECT Max(wt) wt,
                        flight_code
                 FROM   t
                 GROUP  BY
                           flight_code)
        SELECT t.*
        FROM   t,
               t1
        WHERE  t.wt = t1.wt
               AND t.flight_code = t1.flight_code 

Without sub query refactoring you can use like the below

 SELECT t.airplane_code,
           t.day,
           t.flight_code
    FROM   travels t,
           boardings b
    WHERE  t.flight_code = b.flight_code
           AND Trunc(t.day) = Trunc(b.day)
    GROUP  BY t.airplane_code,
              t.day,
              t.flight_code
    HAVING SUM(luggage_weight) IN (SELECT Max(wt) wt
                                   FROM   (SELECT b.day,
                                                  b.flight_code,
                                                  SUM(luggage_weight) wt
                                           FROM   boardings b
                                           GROUP  BY b.day,
                                                     b.flight_code
                                           HAVING SUM(luggage_weight) > 100)rec
                                   WHERE  rec.flight_code = t.flight_code
                                   GROUP  BY t.flight_code)

you can also use something like the below

   SELECT *
    FROM   travels t
    WHERE  ( Trunc(t.day), t.flight_code ) IN (SELECT Trunc(b.day),
                                                      b.flight_code
                                               FROM   boardings b
                                               GROUP  BY b.day,
                                                         b.flight_code
                                               HAVING SUM(luggage_weight) IN
                                                      (SELECT Max(wt) wt
                                                       FROM
                                              (SELECT b.day,
                                                      b.flight_code,
                                                      SUM(luggage_weight)
                                                      wt
                                               FROM   boardings b
                                               GROUP  BY b.day,
                                                         b.flight_code
                                               HAVING SUM(luggage_weight) >
                                                      100)rec
                                                       WHERE
                  rec.flight_code = b.flight_code

Answer 2

You can use dense_rank() in a derived table to rank the flights by the sum() of their weights. Then join that to travels and filter for the rank being 1.

SELECT t.airplane_code,
       t.day,
       t.flight_code
       FROM travels t
            INNER JOIN (SELECT b.day,
                               b.flight_code,
                               dense_rank() OVER (ORDER BY sum(b.luggage_weight) DESC) dr
                               FROM boardings b
                               GROUP BY b.day,
                                        b.flight_code) x
                       ON x.day = t.day
                          AND x.flight_code = t.flight_code
            WHERE x.dr = 1;

db<>fiddle

Answer 3

select Airplane_Code,day,Flight_Code from (
Select Airplane_Code,day,Flight_Code ,sum(Luggage_Weight) over (Partition by 
Flight_Code,day) as total_weight
from travels t,boardings b
where t.Flight_Code=b.Flight_Code)data1
where data1.total_weight >100;