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

Hello 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 whom is enrolled to the HTML course, enrolled twice. Once on 2022-01-04, and once on 20221-01-07.

this query:

SELECT courseNameFk 
FROM Enrollment
WHERE emailFk = '[email protected]'

returns every course the person has been enrolled to. The problem is that the HTML course will ocur 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 ?

CodePudding user response：

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 = '[email protected]'

CodePudding user response：

The accepted answer here might be useful: https://stackoverflow.com/a/51544622/8314250

Also, if you are just trying to avoid duplicates in the returned rows, you can use the DISTINCT keyword.

SELECT DISTINCT courseNameFk 
FROM Enrollment
WHERE emailFk = '[email protected]'