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

# row_number within repeated group of same values

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
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
• ###### 2. Re: row_number within repeated group of same values
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
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.