0

I have this query:

SELECT  VIAGEM.IDVOOREGULAR COD_VOO_REGULAR, 
        VIAGEM.DATAPARTIDA DATA,
        VOO.ORIGEM, 
        VOO.DESTINO, 
        (((EXTRACT (DAY FROM (DATACHEGADA-DATAPARTIDA))*24*60*60 +
           EXTRACT (HOUR FROM (DATACHEGADA-DATAPARTIDA))*60*60 +
           EXTRACT (MINUTE FROM (DATACHEGADA-DATAPARTIDA))*60)/60)-VOO.DURACAO) ATRASO
 FROM   VIAGEM, 
        VOO_REGULAR, 
        VOO
WHERE   VIAGEM.IDVOOREGULAR=VOO_REGULAR.ID AND 
        VOO_REGULAR.IDVOO=VOO.ID;

and this result:

enter image description here

And I want to get just the biggest ATRASO from each group of COD_VOO_REGULAR.

How can I do it?

2

I think something like that should work

SELECT b.* FROM 
(
    SELECT a.*, 
     ROW_NUMBER() OVER (PARTITION BY a.COD_VOO_REGULAR ORDER BY a.ATRASO DESC ) AS rn
    FROM 
    (
      SELECT  VIAGEM.IDVOOREGULAR COD_VOO_REGULAR, 
            VIAGEM.DATAPARTIDA DATA,
            VOO.ORIGEM, 
            VOO.DESTINO, 
            (((EXTRACT (DAY FROM (DATACHEGADA-DATAPARTIDA))*24*60*60 +
               EXTRACT (HOUR FROM (DATACHEGADA-DATAPARTIDA))*60*60 +
               EXTRACT (MINUTE FROM (DATACHEGADA-DATAPARTIDA))*60)/60)-VOO.DURACAO) ATRASO
     FROM   VIAGEM, 
            VOO_REGULAR, 
            VOO
    WHERE   VIAGEM.IDVOOREGULAR=VOO_REGULAR.ID AND 
            VOO_REGULAR.IDVOO=VOO.ID
    )a
) b
WHERE rn = 1 ;
0

You can also use FIRST/LAST function. This one should work as well (not tested), it avoids the sub-query and may provide better performance.

SELECT  VIAGEM.IDVOOREGULAR COD_VOO_REGULAR, 
        MIN(VIAGEM.DATAPARTIDA) KEEP (DENSE_RANK FIRST ORDER BY DATACHEGADA-DATAPARTIDA-VOO.DURACAO) AS DATA,
        MIN(VOO.ORIGEM)         KEEP (DENSE_RANK FIRST ORDER BY DATACHEGADA-DATAPARTIDA-VOO.DURACAO) AS ORIGEM, 
        MIN(VOO.DESTINO)        KEEP (DENSE_RANK FIRST ORDER BY DATACHEGADA-DATAPARTIDA-VOO.DURACAO) AS DESTINO, 
        MAX(((EXTRACT (DAY FROM (DATACHEGADA-DATAPARTIDA))*24*60*60 +
           EXTRACT (HOUR FROM (DATACHEGADA-DATAPARTIDA))*60*60 +
           EXTRACT (MINUTE FROM (DATACHEGADA-DATAPARTIDA))*60)/60)-VOO.DURACAO)) ATRASO
 FROM   VIAGEM, 
        VOO_REGULAR, 
        VOO
WHERE   VIAGEM.IDVOOREGULAR=VOO_REGULAR.ID AND 
        VOO_REGULAR.IDVOO=VOO.ID
GROUP BY VIAGEM.IDVOOREGULAR;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.