It's apparently one of those (long) days again where my brain is unwilling to cooperate any longer .
A simplified testset I'm struggling with on an 11.2.0.3 database:
with t as (
select 1 seq, 10 n from dual union all
select 2 seq, 20 n from dual union all
select 3 seq, 20 n from dual union all
select 4 seq, 30 n from dual union all
select 5 seq, 40 n from dual union all
select 6 seq, 40 n from dual union all
select 7 seq, 10 n from dual union all
select 8 seq, 10 n from dual union all
select 9 seq, 20 n from dual union all
select 10 seq, 20 n from dual
)
select seq
, n
, row_number() over (partition by n order by seq) rn
from t
order by seq;
Here's the output I'm after:
SEQ N RN
---------- ---------- ----------
1 10 1
2 20 1
3 20 2
4 30 1
5 40 1
6 40 2
7 10 1
8 10 2
9 20 1
10 20 2
And this is what I get, since values for N = 10 and 20 are also already present for SEQ = 1 and 2:
SEQ N RN
---------- ---------- ----------
1 10 1
2 20 1
3 20 2
4 30 1
5 40 1
6 40 2
7 10 2
8 10 3
9 20 3
10 20 4
10 rows selected.
Shirley there are several (ugly) ways to get the desired results, but I just can't think of a query that gives the desired output (in one elegant go).
It looks so simple, but I fail to see the solution. Isn't there an analytical way to solve this?
Don't know whether I had way too much coffee or still need more....