with t1 as (select 1111 as id, 1 as cola, 2 as colb, 'B' as colc from dual
union
select 1111 as id, 2 as cola, 1 as colb, 'A' as colc from dual
union
select 1111 as id, 1 as cola, 2 as colb, 'B' as colc from dual
union
select 2222 as id, 2 as cola, 1 as colb, 'A' as colc from dual
union
select 2222 as id, 3 as cola, 2 as colb, 'B' as colc from dual
union
select 3333 as id, 1 as cola, 1 as colb, 'A' as colc from dual
union
select 3333 as id, 2 as cola, 1 as colb, '' as colc from dual
union
select 3333 as id, 2 as cola, 2 as colb, 'B' as colc from dual
union
select 3333 as id, 3 as cola, 1 as colb, 'A' as colc from dual
union
select 3333 as id, 3 as cola, 2 as colb, 'B' as colc from dual
union
select 3333 as id, 4 as cola, 1 as colb, '' as colc from dual
union
select 3333 as id, 5 as cola, 2 as colb, 'B' as colc from dual
union
select 3333 as id, 5 as cola, 2 as colb, 'B' as colc from dual
union
select 3333 as id, 6 as cola, 1 as colb, 'A' as colc from dual
union
select 3333 as id, 7 as cola, 1 as colb, '' as colc from dual
union
select 4444 as id, 1 as cola, 1 as colb, '' as colc from dual
union
select 4444 as id, 1 as cola, 2 as colb, 'B' as colc from dual
union
select 4444 as id, 2 as cola, 1 as colb, 'A' as colc from dual
union
select 4444 as id, 2 as cola, 2 as colb, 'B' as colc from dual
union
select 4444 as id, 3 as cola, 1 as colb, 'A' as colc from dual)
select id, cola, colb, colc,
dense_rank () over (partition by id order by mn, rn) as rn2
from (select id, cola, colb, colc,
min (colb) over (partition by id, cola) as mn,
row_number () over (partition by id order by cola) rn
from t1)
order by id, rn2