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

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