fetch score from previous rank belonging to another student

I'm trying to to fetch score from previous rank belonging to another student for every row in the following select statement. Now, I'd like to have the Score of previous Rank in each GroupCode for every CourseCode and StudentCode.

SELECT StudentCode, CourseCode,GroupCode, Score, StudentRank
FROM Table

my table data

You can use apply :

SELECT StudentCode, CourseCode,GroupCode, Score, StudentRank, t1.What_u_want
FROM Table t OUTER APPLY 
     ( SELECT TOP 1 t1.Score AS What_u_want
       FROM Table t1
       WHERE t1.CourseCode = t.CourseCode AND 
             t1.GroupCode = t.GroupCode AND
             t1.StudentRank < t.StudentRank
             ORDER BY t1.StudentRank DESC
     );

However, same could also achieve with correlation approach :

SELECT StudentCode, CourseCode,GroupCode, Score, StudentRank, 
      (SELECT TOP 1 t1.Score AS What_u_want
       FROM Table t1
       WHERE t1.CourseCode = t.CourseCode AND 
             t1.GroupCode = t.GroupCode AND
             t1.StudentRank < t.StudentRank
             ORDER BY t1.StudentRank DESC
      ) What_u_want
FROM Table t1;