[SOLVED] Top n records per group sql in access

Top n records per group sql in access

I am making some software that tracks the scores of a test. There are multiple users, the details of which are stored in a user table. There is then a progress table which tracks a score with the date and the user who's score it is.

I can already select the 3 most recent records for a chosen userID

SELECT TOP 3 Progress.LoginID, Progress.Score, Progress.[Date Taken]
FROM Progress
WHERE (((Progress.LoginID)=[Enter LoginID:]))
ORDER BY Progress.[Date Taken] DESC;

And I can show all the records grouped by LoginID

SELECT Progress.LoginID, Progress.Score, Progress.[Date Taken]
FROM Progress
GROUP BY Progress.LoginID, Progress.Score, Progress.[Date Taken];

I want to be able to show the 3 most recent records for each user in one query and I'm unsure of how to use nested queries/subqueries to do so.

The field names for the user table are:

LoginID   
Forename    
Surname   
DOB   
Guardian Forename     
Guardian Surname      
Telephone Number

The field names for the progress table are:

ProgressID    
LoginID   
Score     
Date Taken

Any help would be appreciated.

Solution

I had a similar problem a year ago: Top 3 per group including 0

Using the same approach, this will return the latest three dates for each LoginID - you may get more than three records if there are tied dates for the same LoginID.

SELECT  PR1.LogInID, PR1.Score, PR1.[Date Taken]
FROM    Progress AS PR1
WHERE   PR1.[Date Taken] IN (
                        SELECT TOP 3 PR2.[Date Taken]
                        FROM    Progress PR2
                        WHERE   PR2.LoginID = PR1.LoginID
                        ORDER BY PR2.[Date Taken] DESC
                        )
ORDER BY    LoginID, [Date Taken]