求啊这个查询该如何写_Oracle技术

求高手指点啊这个查询该怎么写
表中有如下数据
col1 col2 col3 col4
a aa 1 002
a aa 2 002
a aa 4 002
b bb 1 003
b bb 3 003
c cc 2 003
c cc 0 003
d dd 0 004
d dd 3 004

其中第三列是表示优先级切优先级如下 4>1>3>0>2查询出来的结果是当col1,col2,col4不重复时所有数据都出来当col1,col2,col4重复时就只取col3优先级最高的数据
比如上面的数据最后的结果就是
a aa 4 002
b bb 1 003
c cc 0 003
d dd 3 004
求给具体的语句

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

SQL code

;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,col4from tb)select col1,col2,col3,col4from cte twhere not exists (select 1 from cte where col1=t.col1 and col2=t.col2 and col4=t.col4 and rno>t.rno)
------解决方案--------------------
SQL codeWITH t AS(SELECT 'a'col1,'aa'col2,1 col3,'002'col4 FROM dualUNION ALLSELECT 'a','aa',2,'002' FROM dualUNION ALL SELECT 'a','aa',4,'002' FROM dualUNION ALL SELECT 'b','bb',1,'003' FROM dualUNION ALL SELECT 'b','bb',3,'003' FROM dualUNION ALL SELECT 'c','cc',2,'003' FROM dualUNION ALL SELECT 'c','cc',0,'003' FROM dualUNION ALL SELECT 'd','dd',0,'004' FROM dualUNION 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, COL4a aa 4 002b bb 1 003c cc 0 003d 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