select id,job,score,r t_rank
from(
select g.id, g.job, g.score, row_number()over(partition by job order by score desc) r, start, `end`
from grade g
left join
(select job, (case when max(r)%2=1 then round((max(r)+1)/2,0) else round(max(r)/2,0) end) start, (case when max(r)%2=1 then round((max(r)+1)/2,0) else round(max(r)/2+1,0) end) `end`
from
(select *, row_number()over(partition by job order by score) r
from grade) t1
group by job) t2
on g.job=t2.job) t3
where r in (start,`end`)
order by id