with t1 as ( select '1' c1,'' c2,'' c3,'' c4 from dual union all select '1' c1,'2' c2,'' c3,'' c4 from dual union all select '1' c1,'2' c2,'3' c3,'' c4 from dual union all select '1' c1,'' c2,'' c3,'4' c4 from dual union all select '' c1,'' c2,'3' c3,'' c4 from dual )select c1,c2,c3,c4 from ( select c1,c2,c3,c4, rank() over(order by decode(c1,'',1,0)+decode(c2,'',1,0)+ decode(c3,'',1,0)+decode(c4,'',1,0) desc) rk from t1 ) where rk = 1 c1 c2 c3 c4 -------------------------------- 1 1 2 3
with t1 as
(
select '1' c1,'' c2,'' c3,'' c4 from dual union all
select '1' c1,'2' c2,'' c3,'' c4 from dual union all
select '1' c1,'2' c2,'3' c3,'' c4 from dual union all
select '1' c1,'' c2,'' c3,'4' c4 from dual union all
select '' c1,'' c2,'3' c3,'' c4 from dual
)select c1,c2,c3,c4
from
(
select c1,c2,c3,c4,
rank() over(order by decode(c1,'',1,0)+decode(c2,'',1,0)+
decode(c3,'',1,0)+decode(c4,'',1,0) desc) rk
from t1
)
where rk = 1
c1 c2 c3 c4
--------------------------------
1 1
2 3