Select most recent rows

Question

So, my problem is inserted in this context: I have a system for managing student applications for extra-curricular courses. These applications are stored in a table with the following structure:

• StudentId: the id of the student [Primary Key]
• CourseId: the id of the course [Primary Key]
• StatusVerificationDate: the date when the status of the application was verified [Primary Key]
• Status: the current application status (it can be "In Progress", "Refused", "Admited")

My problem is: For each student and Course, I need to get the most recent row.

StudentId CourseId StatusVarificationDate     Status
1         1001     2011-12-12 07:27:24  In Progress
1         1001     2011-12-12 09:27:24  In Progress
1         1002     2011-12-13 15:27:24  Admited
2         1001     2011-12-12 09:27:24  In Progress
2         1001     2011-12-12 15:27:24  Admited

With the above example, how can I get this result?

StudentId CourseId StatusVerificationDate     Status
1         1001     2011-12-12 09:27:24  In Progress
1         1002     2011-12-13 15:27:24  Admited
2         1001     2011-12-12 15:27:24  Admited

Answer 1

Use ROW_NUMBER() with Partition as below:

;With T AS
(
    SELECT
        *,          
        ROW_NUMBER() OVER(PARTITION BY StudentId,CourseId ORDER BY StatusVarificationDate DESC) AS PartNo
    FROM @tblTest   
)
SELECT
    *   
FROM T
WHERE PartNo=1

Answer 2

This is usually handled using row_number():

select t.*
from (select t.*,
             row_number() over (partition by StudentId, CourseId
                                order by StatusVarificationDate desc
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

Answer 3

Use MAX aggregate function in JOIN statement :

 SELECT *
 FROM table1  
 JOIN
 (
    SELECT StudentId, CourseId MAX(StatusUpdateDate) Maxdate
    FROM table1    
 ) A ON A.Maxdate = StatusUpdateDate AND A.StudentId = table1.StudentId