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

Could you post some sample data and expected results here please? Have you looked into `LAG` as well? — Thom A, Jun 29 '18 at 08:57
If you're using SQL Server 2005 express, I recommend you consider upgrading. Unfortunately, as SQL Server 2005 is so old (it's not been supported at all since April 2016), you can't directly upgrade from SQL Server 2005 to 2017. If you want to update to the latest version, you'll first need to upgrade to a version between 2008 and 2014 (I'd suggest 2014). Then you'll need to do a further upgrade to 2017. Express editions for all these versions are **free** so there's no reason not to upgrade, unless a breaking change affects you (and then you should be looking into fixing them anyway). — Thom A, Jun 29 '18 at 09:31
[Forum Etiquette: How to post data for a T-SQL question](http://www.sqlservercentral.com/articles/Best+Practices/61537/) — Thom A, Jun 29 '18 at 09:47

score 1 · Accepted Answer · answered Jun 29 '18 at 09:53

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;

wonder full your outer apply worked greate .now i wonder what if i wanted the score for two previous rank — bahador arghavan, Jun 29 '18 at 10:28
@bahadorarghavan you'd need to add another sub query. The more (previous) ranks you need the more you'll need (and the slower your query will get as it has the read the table each time). Y — Thom A, Jun 29 '18 at 10:35

score 0 · Answer 2 · edited Jun 29 '18 at 09:22

0

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];

edited Jun 29 '18 at 09:22

Thom A

88,727
11
45
75

answered Jun 29 '18 at 09:17

Ali Akbar

370
3
9

Tanks for your help but i'm using sql server 2005 express. – bahador arghavan Jun 29 '18 at 09:22

fetch score from previous rank belonging to another student

2 Answers2