Oracle – Get Row with the Biggest Value from Each Group

greatest-n-per-grouporacle

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?

Best Answer

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 ;
Related Question