CODEWITHSUNDEEP
sqloracle-database
Mr Black
Mr Black

Reputation: 1

Query with in an Query

Generate a list of pilots that have the highest number of flight hours for each model of plane. Include columns for the pid, the model, and the hourslogged.

This is what I have tried - I am very new to SQL:

select
    pid, hourslogged 
from 
    flightskills f
join    
   (select 
        model, max(hourslogged) as Hourslogged 
    from 
        flightskills
    group by 
        model) h on f.model = h.model

this output is wrong

PID MODEL   HOURSLOGGED
1   A-10        387
4   A-10        387
6   A-10        387
7   A-10        387
3   F-117A  254
4   F-117A  254
5   F-117A  254
6   F-117A  254
1   F-15E   450
2   F-15E   450
3   F-15E   450
4   F-15E   450
6   F-15E   450
1   F-16    827
6   F-16        827
1   F-22        900
3   F-22        900
4   F-22        900
5   F-22        900
6   F-22        900
6   X-100   100

Want something like this

PID MODEL   HOURSLOGGED
1   A-10        387
3   F-117A  254
4   F-15E   450
1   F-16    827
3   F-22        900
6   X-100   100

Upvotes: 0

Views: 77

Answers (3)

YoYo
YoYo

Reputation: 9405

You do not even need to go for a nested query. As you are using Oracle, you can leverage Analytic Functions. Well a little bit of a subquery is still needed, as an analtyic function does not go in a WHERE condition directly (it runs after the where condition is evaluated).

SELECT
  pid, 
  model, 
  hourslogged, 
FROM (
SELECT
  pid, 
  model, 
  hourslogged, 
  ROW_NUMBER() OVER (PARTITION BY model ORDER BY hourslogged DESC) AS rn
FROM flightskills
) WHERE rn = 1

You can use different ranking methodologies instead of the simple ROW_NUMBER. DENSE_RANK for example will treat the situation with two pilots logging the same amount of hours differenly (they will show both up instead of randomly one of two).

Above approach goes directly after the top record by hourslogged for each model. You do this in two steps 1./ Rank 2./ Select top rank (rn = 1).

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

In your query

select pid, hourslogged 
  from flightskills f
  join    
     (select model, max( hourslogged) as Hourslogged 
        from flightskills
       group by model) h    
    on f.model = h.model

there are two PID columns and two HOURSLOGGED columns available when the query is being compiled, f.PID and h.PID, and f.HOURSLOGGED and h.HOURSLOGGED. In the SELECT list you need to tell the database which of the columns you want to use. To fix the problem you might change your query to 

select f.pid, h.hourslogged 
  from flightskills f
  join    
     (select model, max( hourslogged) as Hourslogged 
        from flightskills
       group by model) h    
    on f.model = h.model

dbfiddle here

Best of luck with the rest of your assignment.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I think you are looking for something like this:

select f.*
from flightskills f
where f.hourslogged = (select max(f2.hourslogged) 
                       from flightskills f2
                       where f2.model = f.model
                      );

Upvotes: 1

Related Questions