I have a table as below:
with t1 as
(
select 897654 po_no,1234 pos_no,10 seq_no,1 priority_seq from dual union all
select 897654 po_no,5678 pos_no,10 seq_no,1 priority_seq from dual union all
select 897654 po_no,3423 pos_no,10 seq_no,1 priority_seq from dual union all
select 666777 po_no,3498 pos_no,20 seq_no,1 priority_seq from dual union all
select 666888 po_no,3479 pos_no,20 seq_no,2 priority_seq from dual union all
select 897654 po_no,3423 pos_no,10 seq_no,2 priority_seq from dual union all
select 897654 po_no,1111 pos_no,10 seq_no,3 priority_seq from dual union all
select 123456 po_no,5555 pos_no,20 seq_no,3 priority_seq from dual union all
select 666777 po_no,3498 pos_no,30 seq_no,4 priority_seq from dual
) select * from t1;
for a given po_no,pos_no and seq_no is the PK for this table data.
I need a rank based on the po_no and the priority_seq.
if the po_no belongs to lower priority_seq then that is the higher priority.
The po_no:897654 which are belong to priority_seq=1 I need rank of 1
The po_no:897654 which are belong to priority_seq=2 I need rank of 2
The po_no:897654 which are belong to priority_seq=3 I need rank of 2 or 3
Finally I will take the data that has rank as 1
I need output as below.
<img src="https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/YXCDOWMTUYQ1/image.png" alt="image.png">
Thanks,