update scores set ranking  = sub.rank 
from (
    select distinct subject_code, score, rank() over ( partition by subject_code order by score desc ) as rank from scores 
    ) as sub
where scores.subject_code = sub.subject_code
    and scores.score = sub.score