--科目別に各学生の順位を算出
WITH subq AS (
    SELECT
        student_code
        ,subject_code
        ,rank () OVER(PARTITION BY subject_code ORDER BY score DESC) AS subqrank
    FROM
        scores
        )
        
UPDATE
    scores
SET
    ranking = subq.subqrank
FROM
    subq
WHERE
    scores.student_code = subq.student_code
    AND scores.subject_code = subq.subject_code;
    
--SELECT * FROM scores;