I´m learning PL SQL and I ran into a wall...
So I have this ER model:
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
and H.NHORASEFETUADAS = (select distinct max(o.NHORASEFETUADAS)
FROM HORASVOO o
WHERE o.IDTRIPULANTE = T.IDTRIPULANTE)
ORDER BY A.IDTIPOAVIAO;
You could use
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;
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.