6 Replies Latest reply: Nov 22, 2012 12:20 PM by user10857924 RSS

    Analytic functions --- How to partition with respect to the order?

    925144
      Imagine I have a table like this:
       N     L1       L2
      -------------------
       1      A        5
       2      A        6
       3      B        7
       4      C        3
       5      B        2
       6      B        1
      I want to return
       N     L1       L2
      -------------------
       2      A        6
       3      B        7
       4      C        3
       5      B        2
      That is the row with the maximum L2 among the each group of L1, but the grouping made with respect to N, that is there are 2 groups of 'B' as you see.

      How od I do this?
        • 1. Fixed Difference
          Frank Kulash
          Hi,

          Here's one way:
          WITH     got_grp          AS
          (
               SELECT     n, l1, l2
               ,     ROW_NUMBER () OVER ( ORDER BY          n )
                     - ROW_NUMBER () OVER ( PARTITION BY  l1
                                               ORDER BY          n
                                 )         AS grp
               FROM     table_x
          )
          SELECT       MAX (n) KEEP (DENSE_RANK LAST ORDER BY l2)
                                      AS n
          ,       l1
          ,       MAX (l2)     AS l2
          FROM       got_grp
          GROUP BY  grp, l1
          ORDER BY  n
          ;
          If you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could test this.

          For an explanation of the Fixed Difference technique, see {message:id=9953384} and/or      {message:id=9957164}
          • 2. Re: Analytic functions --- How to partition with respect to the order?
            bencol
            with t (n,l1,l2)
            as (select 1,'A',5 from dual
            union all select 2,'A',6 from dual
            union all select 3,'B',7 from dual
            union all select 4,'C',3 from dual
            union all select 5,'B',2 from dual
            union all select 6,'B',1 from dual
            )
            select n
                  ,l1
                  ,l2
            from   (select n
                          ,l1
                          ,l2
                          ,row_number() over (partition by l1,rn order by l2 desc) rn2
                          ,rn
                    from   (select n
                                  ,l1
                                  ,l2
                                  ,row_number() over (order by n)
                                 - row_number() over (partition by l1 order by n) rn
                            from t
                           )
                   )
            where rn2 = 1
            order by n
            /
              N L         L2
            ___ _ __________
              2 A          6
              3 B          7
              4 C          3
              5 B          2
            • 3. Re: Analytic functions --- How to partition with respect to the order?
              ranit B
              A different way...
              with xx as(
                  select 1 n,'A' l1,5 l2 from dual union all
                  select 2 n,'A' l1,6 l2 from dual union all
                  select 3 n,'B' l1,7 l2 from dual union all
                  select 4 n,'C' l1,3 l2 from dual union all
                  select 5 n,'B' l1,2 l2 from dual union all
                  select 6 n,'B' l1,1 l2 from dual 
               )
               select n,l1,l2 from xx
               minus
               select n,l1,l2 from(
                        select n,l1,l2,count(l2) over (partition by l1) cnt,row_number() over (partition by l1 order by l2 ) rn from xx
                ) where rn = 1 and cnt > rn;
              gives
              2     A     6
              3     B     7
              4     C     3
              5     B     2
              HTH
              Ranit B.
              • 4. Re: Analytic functions --- How to partition with respect to the order?
                user10857924
                or may be like this...
                select N, L1, L2
                from
                (
                     select x.*, rank()over(partition by grp order by L2 desc nulls last) as rnk
                     from
                     (
                          select x.*, sum(flg)over(order by n) as grp
                          from
                          (
                               select t.*, 
                               case when L1!=max(L1) over(order by N rows between 1 preceding and 1 preceding ) OR max(L1) over(order by N rows between 1 preceding and 1 preceding ) is null Then 1 END as flg
                               from t
                          )X 
                     )X 
                )x where rnk=1     
                • 5. Re: Analytic functions --- How to partition with respect to the order?
                  bencol
                  user10857924 wrote:
                  case when L1!=max(L1) over(order by N rows between 1 preceding and 1 preceding )
                            OR max(L1) over(order by N rows between 1 preceding and 1  preceding ) is null Then 1 END as flg
                  
                   
                  Isn't that a complicated way of doing this:
                  case when L1 != lag(L1) over(order by N) Then 1 END as flg
                  ?
                  • 6. Re: Analytic functions --- How to partition with respect to the order?
                    user10857924
                    Agree :) thanks!!

                    Edited by: user10857924 on Nov 22, 2012 1:20 PM