select id,r,sr,kbn from (
select distinct id
,row_number() over(partition by id order by r desc) rn
,row_number() over(partition by id,r order by sr desc) rn2
,row_number() over(partition by id,r,sr order by kbn desc) rn3
,r
,sr
,kbn
from test_soukou
)
where rn = 1 and rn2=1 and rn3=1
select distinct id
,row_number() over(partition by id order by r desc) rn
,row_number() over(partition by id,r order by sr desc) rn2
,row_number() over(partition by id,r,sr order by kbn desc) rn3
,r
,sr
,kbn
from test_soukou
)
where rn = 1 and rn2=1 and rn3=1
--order by id,v1,v2,v3
select a.id, a.r, a.sr, max(kbn) as kbn
from
(
select t.id, t.r, max(sr) as sr
from (select id, max(r) as r from test_soukou group by id) t1, test_soukou t
where t1.id = t.id and t1.r = t.r group by t.id, t.r
) a, test_soukou b
where a.id = b.id and a.r=b.r and a.sr=b.sr group by a.id,a.r,a.sr