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

Sample data and expected output would help
– Ajay Gupta
Jun 29 at 8:57

Could you post some sample data and expected results here please? Have you looked into LAG as well?
– Larnu
Jun 29 at 8:57

LAG

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).
– Larnu
Jun 29 at 9:31

Forum Etiquette: How to post data for a T-SQL question
– Larnu
Jun 29 at 9:47

2 Answers
2

You can use apply :

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 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
– Larnu
Jun 29 at 10:35

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


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

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.