How to get every record which date attribute is the most recent?

Question

I am trying to get every course where the email matches a specific one from an enrollment table.
I only want to get the most recent enrollment from each course. As you can see in the image, the person who is enrolled in the HTML course, enrolled twice. Once on 2022-01-04, and once on 20221-01-07.

this query:

SELECT courseNameFk 
FROM Enrollment
WHERE emailFk = 'lsmeuin9@biglobe.ne.jp'

returns every course the person has been enrolled to.
The problem is that the HTML course will occur twice in the results:

What I want is that only the most recent enrollment of each course shows up, but I have no idea how I should write the query to exclude the older enrollments.
Is there someone who can help me?

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Jan 09 '22 at 00:25

score 1 · Accepted Answer · answered Jan 08 '22 at 05:07

Try Following SQL Code:

SELECT d.emailFk,
       d.courseNameFk,
       d.registrationDate,
       d.MainID FROM (
select emailFk , courseNameFk , registrationDate , ROW_NUMBER() OVER (PARTITION BY emailFk , courseNameFk  ORDER by registrationDate DESC) as MainID
FROM Enrollment) d WHERE d.MainID = 1

In this way, you can retrieve the last record recorded by date for each email and each lesson, and finally, using the email condition, you can access the person you want.

SELECT d.emailFk,
       d.courseNameFk,
       d.registrationDate,
       d.MainID FROM (
select emailFk , courseNameFk , registrationDate , ROW_NUMBER() OVER (PARTITION BY emailFk , courseNameFk  ORDER by registrationDate DESC) as MainID
FROM Enrollment) d WHERE d.MainID = 1
AND d.emailFk = 'lsmeuin9@biglobe.ne.jp'

How to get every record which date attribute is the most recent?

1 Answers1