SELECT student_code, subject_code, 
RANK() OVER(PARTITION BY subject_code ORDER BY score DESC)AS rank
FROM scores;

UPDATE scores AS SC
SET ranking = SubSC.rank
FROM(SELECT student_code, subject_code,
RANK() OVER(PARTITION BY subject_code ORDER BY score DESC)
AS rank
FROM scores)AS SubSC
WHERE SC.student_code = SubSC.student_code
AND SC.subject_code = SubSC.subject_code