fetch score from previous rank belonging to another student

Question

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

Answer 1

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;

Answer 2

You can use LAG Command to get the previous value

SELECT LAG(StudentCode) prev_StudentCode,
       StudentCode,
       LAG(CourseCode) prev_CourseCode,
       CourseCode,
       LAG(GroupCode) prev_GroupCode,
       GroupCode,
       LAG(Score) prev_Score,
       Score,
       LAG(StudentRank) prev_StudentRank,
       StudentRank
FROM [Table];