SQL inner join with max

Question

I have an exercise on queries and I can't seem to find the solution to one of them. I have two tables : Aircrafts and Certified.

In the table Aircrafts I have the information AID (ID of the plane), Aname (Name of the plane) and Crusingrange(Max distance the plane have) :

AID    Aname       Crusingrange            
1     BoeingFr        25000   
2     BoeingUS        50000   
3      Jet01          3000  
4      Jet02          4000

In the table CERTIFIED I have this information AID (ID of the plane) and EID (ID of the pilot) :

What I want is the ID of the pilot and the ID of the plane with the greatest cruising range he/she can fly.

I know I have to use MAX within INNER JOIN but I really don't find the solution and I have tried to break down my code but still impossible.

Thank you

@MadhurBhaiya for this question is doesn't really matter because the needed SQL is pretty general.. This pretty much coverd here on stackoverflow.. — Raymond Nijland, Oct 12 '18 at 17:17
@RaymondNijland CruisingRange is in Airplanes table. OP needs to find the airplanes with max cruisingrange for an employee. — Madhur Bhaiya, Oct 12 '18 at 17:22
Yea @MadhurBhaiya never mind i didn't read `ID of the planes with the greatest cruising range he/she can fly.` the first time.. — Raymond Nijland, Oct 12 '18 at 17:23

Madhur Bhaiya · Accepted Answer · 2018-10-12T17:23:39.573

0

Firstly, get maximum value of Crusingrange for a EID, using Join and Group By. Use this result as a Derived table.
Now, again Join back to the main tables using maximum value of Crusingrange and EID.

For MySQL, try the following:

SELECT c1.EID, 
       a1.AID
FROM CERTIFIED AS c1
JOIN Aircrafts AS a1 ON a1.AID = c1.AID 
JOIN        
(
  SELECT c.EID, 
         MAX(a.Crusingrange) AS Crusingrange 
  FROM CERTIFIED AS c
  JOIN Aircrafts AS a ON a.AID = c.AID 
  GROUP BY c.EID 
) AS dt ON dt.Crusingrange = a1.Crusingrange AND 
           dt.EID = c1.EID

edited Oct 12 '18 at 17:23

answered Oct 12 '18 at 17:18

Madhur Bhaiya

28,155
10
49
57

1

thank you it works perfectly – Antoine BATTAGLIOTTI Oct 12 '18 at 19:40

Oto Shavadze · Answer 2 · 2018-10-12T18:00:44.297

0

If your DB supports row_number() window function, then you can join your tables, then sort rows per EID by Crusingrange, and choose longest ones:

SELECT EID, AID FROM (
    SELECT CERTIFIED.AID, CERTIFIED.EID, row_number() over(partition by CERTIFIED.EID ORDER BY Aircrafts.Crusingrange DESC) rn
    FROM CERTIFIED
    JOIN Aircrafts
    ON CERTIFIED.AID = Aircrafts.AID
) t
WHERE rn = 1

edited Oct 12 '18 at 18:00

answered Oct 12 '18 at 17:54

Oto Shavadze

40,603
55
152
236

"If your DB supports row_number() window function," For MySQL this query requires MySQL 8.0+ before it works.. – Raymond Nijland Oct 12 '18 at 18:36
@RaymondNijland - Yep, it's not clear which DB and version OP uses exactly, so I have "If your DB supports..." – Oto Shavadze Oct 12 '18 at 18:39

SQL inner join with max

2 Answers2