Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

row_number within repeated group of same values

UW (Germany)Mar 23 2011 — edited Mar 23 2011
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
This post has been answered by Frank Kulash on Mar 23 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 20 2011
Added on Mar 23 2011
3 comments
2,585 views