How to Retrieve the Top N Records for Each User in Access SQL?

Access SQL: Extracting the Top N Records per User

This guide demonstrates how to retrieve the top three most recent records for each user within an Access database using a single SQL query. We'll leverage nested queries for this task.

Database Structure

Our database consists of two tables:

• User Table: LoginID, Forename, Surname, DOB, Guardian Forename, Guardian Surname, Telephone Number
• Progress Table: ProgressID, LoginID, Score, Date Taken

The Solution: Nested Queries

A straightforward approach using TOP N within a single query is not directly supported in Access SQL for this specific requirement (retrieving the top N for each group). Instead, we'll use a subquery to achieve this:

The inner query identifies the top three Date Taken values for each LoginID:

<code class="language-sql">SELECT TOP 3 PR2.[Date Taken]
FROM Progress AS PR2
WHERE PR2.LoginID = PR1.LoginID
ORDER BY PR2.[Date Taken] DESC</code>

This result set is then used in the outer query's IN clause to filter the Progress table:

<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 AS PR2
    WHERE PR2.LoginID = PR1.LoginID
    ORDER BY PR2.[Date Taken] DESC
)
ORDER BY PR1.LoginID, PR1.[Date Taken] DESC;</code>

This final query efficiently returns the three most recent records for each LoginID, neatly ordered by LoginID and then by Date Taken in descending order (most recent first). This avoids the limitations of directly applying TOP N to grouped data within Access SQL.

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