UPDATE scores
SET ranking = sub.rank
from (SELECT subject_code,RANK() OVER(PARTITION BY subject_code ORDER BY score DESC) as rank FROM scores) as sub
WHERE scores.subject_code = sub.subject_code;

--select * from scores;