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