update scores
set ranking = s2.ranking
from(
    SELECT 
        student_code
        ,subject_code
        ,RANK() OVER(PARTITION BY subject_code ORDER BY score DESC) as ranking
    FROM 
        scores
) as s2
where scores.student_code = s2.student_code
    and scores.subject_code = s2.subject_code;
    
select * from scores