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

Retrieve top-level records for each group using Access SQL

In Access, you can use a subquery to display the three most recent records for each user. Here’s how:

<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:

• External query (PR1) selects the LoginID, Score and Date Taken fields from the Progress table.
• Subquery (PR2) retrieves the first three Date Taken values ​​for each LoginID, in descending order.
• The WHERE clause in the outer query ensures that the value selected in PR1 is among the first three results for its respective LoginID.
• The final ORDER BY clause sorts the results by LoginID and Date Taken for easier viewing.

This query will allow you to display the three most recent records for each user in the progress meter, giving you a concise summary of their progress over time.

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