filipe filipe - 8 days ago 5
SQL Question

Learning PL SQL, simple select

I´m learning PL SQL and I ran into a wall...

So I have this ER model: enter image description here

And I want to get the Tripulante that has the most nHorasEfectuadas on each TipoAviao. (The id of the crew that has the most number of hours in each plane type).

I tried this :

SELECT T.IDTRIPULANTE, A.IDTIPOAVIAO, H.NHORASEFETUADAS
FROM TRIPULANTE T, HORASVOO H, TIPOAVIAO A
WHERE T.IDTRIPULANTE=H.IDTRIPULANTE
and H.IDTIPOAVIAO=A.IDTIPOAVIAO
and H.NHORASEFETUADAS = (select distinct max(o.NHORASEFETUADAS)
FROM HORASVOO o
WHERE o.IDTRIPULANTE = T.IDTRIPULANTE)
ORDER BY A.IDTIPOAVIAO;


But its returning ALL of the Tripulantes...(Only have 10)

enter image description here

Anything I'm doing wrong?

Thanks!

Answer

You could use ROW_NUMBER:

SELECT IDTIPOAVIAO, NHORASEFETUADAS, IDTRIPULANTE
FROM (
        SELECT     A.IDTIPOAVIAO, 
                   H.NHORASEFETUADAS,
                   T.IDTRIPULANTE,
                   ROW_NUMBER() OVER (PARTITION BY A.IDTIPOAVIAO 
                                      ORDER BY NHORASEFETUADAS DESC) RN
        FROM       TIPOAVIAO A
        INNER JOIN HORASVOO H
                ON H.IDTIPOAVIAO=A.IDTIPOAVIAO 
        INNER JOIN TRIPULANTE T 
                ON T.IDTRIPULANTE=H.IDTRIPULANTE) X
WHERE RN = 1
ORDER BY IDTIPOAVIAO;

Explanation

inner join replaces what you had in the where clause, but that way is really old-fashioned. join is the ISO standard way of expressing how tables relate to each other.

row_number is a window function, which gives the current record a number: the sequence number it has within a subsection of records (partition) defined in the over clause: those with the same IDTIPOAVIAO value and in order of decreasing NHORASEFETUADAS. That way you know that the ones that get number 1 have the highest NHORASEFETUADAS in that partition.

Comments