CODEWITHSUNDEEP
sqloracle-database
filipe
filipe

Reputation: 69

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!

Upvotes: 0

Views: 60

Answers (3)

trincot
trincot

Reputation: 350290

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.

Upvotes: 2

Dmitriy
Dmitriy

Reputation: 5565

I hope I understood you correctly:

SELECT A.IDTIPOAVIAO, max(T.IDTRIPULANTE) keep (dense_rank first order by H.NHORASEFETUADAS desc)
FROM TRIPULANTE T join HORASVOO  H on T.IDTRIPULANTE=H.IDTRIPULANTE
                  join TIPOAVIAO A on H.IDTIPOAVIAO=A.IDTIPOAVIAO 
group by A.IDTIPOAVIAO

Upvotes: 1

JanneP
JanneP

Reputation: 587

Create a temp table or a view from IDTIPOAVIAO and NHORASEFETUADAS. Try something like this:

CREATE TABLE TMP_ID AS
SELECT A.IDTIPOAVIAO AS ID,(SELECT MAX(H.NHORASEFETUADAS) FROM 
  HORASVOO H WHERE H.IDTIPOAVIAO=A.IDTIPOAVIAO) AS HIGHESTVALUE
FROM TIPOAVIAO A
GROUP BY A.IDTIPOAVIAO

SELECT H.IDTRIPULANTE, T.ID, T.HIGHESTVALUE
FROM HORASVOO H
INNER JOIN TMP_ID T ON (H.IDTIPOAVIAO=T.ID AND H.NHORASEFETUADAS=T.HIGHESTVALUE)

Upvotes: 0

Related Questions