I have the following table for evaluating students:
StudentID | EvaluationStatusID| Date
1011010 | 1 |2013-11-07 20:31:51.000
1011020 | 1 |2013-11-08 13:23:51.000
1011010 | 2 |2013-11-08 20:31:51.000
1011020 | 3 |2013-11-09 20:31:51.000
The evaluation of a student does through different stages - 'submitted','assessed,'accepted' etc.
I need to get the LATEST record(by date) on each student in teh form 'StudentID-EvaluationStatusID'.
So,in the above data i should have the following returned:
1011010-2
1011020-3
In Sql server 2008,how do I get this?
The simplest is using ranking functions like ROW_NUMBER.
WITH CTE AS
(
SELECT StudentID, EvaluationStatusID, Date,
RN = ROW_NUMBER() OVER (PARTITION BY StudentID
ORDER BY Date DESC)
FROM dbo.Student
)
SELECT StudentID, EvaluationStatusID, Date
FROM CTE WHERE RN = 1
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments