SQL inner join with max

Antoine BATTAGLIOTTI

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

Madhur Bhaiya

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-12-1

Comments

0 comments

TOP Ranking

Article

SQL inner join with max

SQL inner join with max

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

pump.io port in URL

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

grouping by column variables and appending a new variable based on condition

Python Read Directory And Output to CSV

BigQuery - concatenate ignoring NULL

Angular 8. Unknown amount of http.get requests in array to call, must be sequential, what to use

Remove adjacent duplicates in linked list in C

Can a 32-bit antivirus program protect you from 64-bit threats

How to keep curl session alive between two php processes?

Limit number of characters in uitextview

Unable to use switch toggle for dark mode in material-ui

In C#, is there a way to create a List directly from an Array without copying?

Laravel getting value from another table using eloquent

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

MTKView Displaying Wide Gamut P3 Colorspace

Vector input in shiny R and then use it

Modify c# Windows Forms control library

SQL Server : are transaction locking table for other users?

When I click any button in my view page the form is submitted

Can you sort columns (horizontally) in Google Sheets?