;with cte as
(
select col1,col2,(case when col3 = 4 then 4 when col3 = 1 then 3 when col3 = 3 then 2
                       when col3 = 0 then 2 when col3 = 2 then 1 else 0 end) rno,col3,col4
from tb
)

select col1,col2,col3,col4
from cte t
where not exists (select 1 from cte where col1=t.col1 and col2=t.col2 and col4=t.col4 and rno>t.rno)

------解决方案--------------------

SQL code
WITH t AS(
SELECT 'a'col1,'aa'col2,1 col3,'002'col4 FROM dual
UNION ALL
SELECT 'a','aa',2,'002' FROM dual
UNION ALL 
SELECT 'a','aa',4,'002' FROM dual
UNION ALL 
SELECT 'b','bb',1,'003' FROM dual
UNION ALL 
SELECT 'b','bb',3,'003' FROM dual
UNION ALL 
SELECT 'c','cc',2,'003' FROM dual
UNION ALL 
SELECT 'c','cc',0,'003' FROM dual
UNION ALL 
SELECT 'd','dd',0,'004' FROM dual
UNION ALL 
SELECT 'd','dd',3,'004' FROM dual
)
SELECT col1,col2,col3,col4 
FROM 
(
  SELECT 
  col1,
  col2,
  col3,
  col4,
  Row_Number() over (PARTITION BY col1,col2,col4 ORDER BY Decode(col3,4,1,1,2,3,3,0,4,2,5)) rn
  FROM t 
)WHERE rn=1;

output：
COL1, COL2, COL3, COL4
a aa 4 002
b bb 1 003
c cc 0 003
d dd 3 004

------解决方案--------------------
添加假排序编号查询

------解决方案--------------------
select t.col1,t.col2,substr('41302',min(t.col3),1) col3,t.col4 from (select tb.col1,tb.col2,instr(to_char(tb.col3),'41302') col3,tb.col4 from tb) t grop by t.col1,t.col2,t.col4