UPDATE scores AS s
SET ranking  = sub.rank
FROM(SELECT student_code, RANK() OVER(PARTITION BY subject_code ORDER BY score DESC) AS rank
FROM scores GROUP BY student_code) AS sub
WHERE s.student_code = sub.student_code;

SELECT * FROM scores ORDER BY subject_code ASC, ranking ASC;