How to Retrieve the Top N Records per Group in Microsoft Access SQL?

Use SQL (Access) to get the top N records of each group

Question:

How to get the n latest records of each group using SQL in Microsoft Access?

Background:

Users have tables named "User" and "Progress" which contain information about the user and their score respectively. They need a query to display the three most recent scores for each user.

Answer:

The following nested query retrieves the three most recent scores for each user:

<code class="language-sql">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]</code>

Instructions:

The subquery within the

IN clause uses the TOP keyword and the ORDER BY clause to retrieve the three latest dates for each LoginID. The main query then selects the corresponding records from the Progress table where the [Date Taken] column matches one of the three latest dates for each LoginID. The ORDER BY clause in the main query ensures that the records are sorted by LoginID and [Date Taken] in descending order.

The above is the detailed content of How to Retrieve the Top N Records per Group in Microsoft Access SQL?. For more information, please follow other related articles on the PHP Chinese website!