with t As ( select *,row_number() over(partition by job order by score desc) r_rank from grade) select t.id,t.job,t.score,t.r_rank from t left join (select job, (case when mod(max(r_rank),2)=0 then max(r_rank)/2 else floor(max(r_rank)/2)+1 end) start, ...