This discussion is archived
6 Replies Latest reply: Nov 22, 2012 10:20 AM by user10857924 RSS

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

925144 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Agree :) thanks!!

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points