3 Replies Latest reply: Mar 23, 2011 6:53 AM by Frank Kulash RSS

    row_number within repeated group of same values

    UW (Germany)
      I think there should be a way to solve the following problem with analytic functions but I don't see the solution right now :-(
      Maybe someone can help me. I have a table like this
      create table demo
      (n number,
       t varchar2(1));
       
      insert into demo values (1,'A');
      insert into demo values (2,'A');
      insert into demo values (3,'B');
      insert into demo values (4,'C');
      insert into demo values (5,'C');
      insert into demo values (6,'C');
      insert into demo values (7,'A');
      insert into demo values (8,'A');
      insert into demo values (9,'B');
      insert into demo values (10,'C');
      and I want to get the row_number within a group of equal letters in column t, ordered by column n, meaning that counting should start with 1 again, when 'A' appears once more after there where other letters between.
      When I use something like row_number() over (partition by t order by n) counting the A's continues with 3, where it should start with 1 again and so on
      select 
         n, 
         t,
         row_number() over (partition by t order by n) v
      from demo
      order by n;
      
      N  T V Should be 
      -- - - ---------
      1  A 1         1
      2  A 2         2
      3  B 1         1
      4  C 1         1
      5  C 2         2
      6  C 3         3
      7  A 3         1
      8  A 4         2
      9  B 2         1
      10 C 4         1
        • 1. Re: row_number within repeated group of same values
          Etbin
          NOT TESTED !
          select n,t,row_number() over (partition by grp order by n) v
            from (select n,t,last_value(frst ignore nulls) over (partition by null order by n) grp
                    from (select n,t,case when t != lag(t,1,t) over (order by n) then t end frst
                            from demo
                         )
                 )
          Regards

          Etbin

          Edited by: Etbin on 23.3.2011 13:08
          partition by null added
          • 2. Re: row_number within repeated group of same values
            UW (Germany)
            For the A's it starts with 1 again, but not for the B's and C's
            select n,t,row_number() over (partition by grp order by n) v
              from (select n,t,last_value(frst ignore nulls) over (order by n) grp
                      from (select n,t,case when t != lag(t,1,t) over (order by n) then t end frst
                              from demo
                           )
                   )
            order by n
            
            N  T V  
            -- - ---
            1  A 1  
            2  A 2  
            3  B 1  
            4  C 1  
            5  C 2  
            6  C 3  
            7  A 1  
            8  A 2  
            9  B 2  
            10 C 4  
            • 3. Re: row_number within repeated group of same values
              Frank Kulash
              Hi,

              Here's one way:
              WITH     got_grp_num     AS
              (
                   SELECT     n, t
                   ,     ROW_NUMBER () OVER ( ORDER BY      n)
                         - ROW_NUMBER () OVER ( PARTITION BY  t
                                       ORDER BY      N
                                     )     AS grp_num
                   FROM     demo
              --     WHERE     ...     -- If you need any filtering, put it here
              )
              SELECT       n, t
              ,       ROW_NUMBER () OVER ( PARTITION BY  t
                                    ,          grp_num
                                    ORDER BY          n
                                )     AS v
              FROM       got_grp_num
              ORDER BY  n
              ;
              Output:
              `        N T          V
              ---------- - ----------
                       1 A          1
                       2 A          2
                       3 B          1
                       4 C          1
                       5 C          2
                       6 C          3
                       7 A          1
                       8 A          2
                       9 B          1
                      10 C          1
              Etbin had the right approach; you need to PARTITION BY something finer than t; it has to be a sub-division of t, where all consecutive rows with the same t are in the same group. You could get that group number using LAG or LEAD, like Etbin tried, but it typically takes 2 sub-queries.