Funciones Analiticas: Over Partition

En la siguiente consulta SQL, se quiere obtener los planes que cumplan ciertos criterios, y ademas, cuya fecha de finalizacion sea la maxima.
Para ello, la subconsulta busca la máxima fecha de finalización de un plan determinado por la consulta principal.

Select pro.Id_Promo,
       pro.Fecha,
       amo.Stc_Id,
       pla.Flag
  From Promo Pro,
       Amounts Amo,
       Plans Pla
 Where pro.Fecha_Ini = ‘AD’
   And pro.Estado = ‘P’
   And amo.Rpl_Id = pro.Id_Promo
   And amo.Stc_Id In (‘GT’)
   And pla.Rpl_Id = Amo.Rpl_Id
   And amo.End_Date = (Select Max(End_Date)
                         From Amounts a
                        Where a.Rpl_Id = c.Id_Promo
                          And a.Stc_Id In (‘GT’));

Y si pudiéramos calcular esto mismo en la consulta principal? Sabemos que las funciones de filas, solo devuelven un valor escalar, por lo cual, contariamos unicamente con un numero y no sabríamos a que plan pertenece.

Podríamos usar un GROUP BY, pero nuevamente caemos en la restricción de que solo se puede agrupar por las columnas de selección, y si deseamos obtener el Id_Promo, Fecha, Stc_id y Flag, tendriamos que agrupar por todos estos campos, y nuestra maxima fecha buscada ya no seria la maxima para el plan, sino la maxima para el subgrupo formado.

Con OVER PARTITION, podemos simular un GROUP BY, y calcular el máximo por grupo. Y luego, quedarnos solo con el máximo.

Select Id_Promo,
       Fecha,
       Stc_Id,
       Flag,
       End_Date
  From (Select /*+ ordered full(c) use_hash(c,fra) use_hash(fra,rpl)*/
               pro.Id_Promo,
               pro.Fecha,
               amo.Stc_Id,
               pla.Flag,
               Amo.End_Date,
               Max(Amo.End_Date) Over(Partition By Amo.Rpl_Id) As Rmax_End_Date
          From Promo Pro,
               Amounts Amo,
               Plans Pla
         Where pro.Fecha_Ini = ‘AD’
           And pro.Estado = ‘P’
           And amo.Rpl_Id = pro.Id_Promo
           And amo.Stc_Id In (‘GT’)
           And pla.Rpl_Id = Amo.Rpl_Id)
 Where End_Date = Rmax_End_Date;