Get row with the biggest value from each group

Question

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:

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

How can I do it?

Answer 1

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 ;

Answer 2

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;