Learning PL SQL, simple select

Question

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
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)

Anything I'm doing wrong?

Thanks!

Note that your question is not about PL SQL (the procedural extension), but about Oracle SQL. — trincot
– trincot, Commented Nov 24, 2016 at 21:19

trincot · Accepted Answer · 2016-11-24 21:44:56Z

2

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.

edited Nov 24, 2016 at 21:44

answered Nov 24, 2016 at 21:10

trincot

357k38 gold badges282 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

filipe Over a year ago

Thanks! Its exactly what i was looking for. Now i just have to understand how and when to use ROW_NUMBER () and the inner joins...

Dmitriy · Accepted Answer · 2016-11-24 21:17:35Z

1

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

answered Nov 24, 2016 at 21:17

Dmitriy

5,58512 gold badges27 silver badges39 bronze badges

Comments

JanneP · Accepted Answer · 2016-11-24 21:11:26Z

0

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)

answered Nov 24, 2016 at 21:11

JanneP

5874 silver badges13 bronze badges

Collectives™ on Stack Overflow

Learning PL SQL, simple select

3 Answers 3

Explanation

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Explanation

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related