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

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