update scores set ranking  = sub.rank 
from 
     ( select subject_code, rank() over ( partition by subject_code order by score ) as rank from scores
         group by subject_code ) as sub
where scores.subject_code = sub.subject_code