Here's one way to approach this:
- Find the first flag value for each person using first_value
- Use this calculated first flag to rank() the results:
create table test_table (
ord number, person_no number, flag number
);
insert into test_table values (2999362,3754090,10);
insert into test_table values (2999362,3807718,8);
insert into test_table values (2999362,3991003,9);
insert into test_table values (2999362,5475332,7);
insert into test_table values (2999362,5475332,5);
insert into test_table values (2999362 ,10750089,4);
insert into test_table values (2999362, 11150508,6);
insert into test_table values (2999362, 12158176,3);
insert into test_table values (2999362, 21065699,1);
insert into test_table values (2999362, 21065699,2);
commit;
with first_flags as (
select t.*,
first_value ( flag ) over (
partition by person_no order by flag
) fv_flag
from test_table t
)
select ord, person_no,
rank () over (
order by fv_flag
) rk
from first_flags
order by flag;
ORD PERSON_NO RK
2999362 21065699 1
2999362 21065699 1
2999362 12158176 3
2999362 10750089 4
2999362 5475332 5
2999362 11150508 7
2999362 5475332 5
2999362 3807718 8
2999362 3991003 9
2999362 3754090 10