Grouping rows based on a date field in SQL Server

I have the following table exams.sql with exam scores in SQL Server. It saves the student’s ID, their score, and the test date.

CREATE TABLE exams (
  student_id INTEGER,
  score INTEGER,
  date DATE
);

INSERT INTO exams VALUES (1, 2, '2021-01-06');
INSERT INTO exams VALUES (1, 8, '2021-01-09');
INSERT INTO exams VALUES (2, 10, '2021-02-14');
INSERT INTO exams VALUES (3, 9, '2021-02-26');
INSERT INTO exams VALUES (3, 7, '2021-03-30');

The problem begins when a student takes the exam two (or more) times. I need the complete list of students with their actual qualification: in case they have been evaluated more than once, take the last date.

student_id	score	date
1	2	2021-01-06
1	8	2021-01-09
2	10	2021-02-14
3	9	2021-02-26
3	7	2021-03-30

The result of the query should be:

student_id	score	date
1	8	2021-01-09
2	10	2021-02-14
3	7	2021-03-30

This was my attempt:

Select student_id, score, max(date) from exams
Group by student_id

And I get the following error message.

Column 'score' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

But I can’t add ‘score’ to ‘group by’ since being different scores, I would have several rows per student.

Answer

You can use row_number():

select e.*
from (select e.*,
             row_number() over (partition by student_id order by date desc) as seqnum
      from exams e
     ) e
where seqnum = 1;

Advertisement

Answer