Top n records per group sql in access

by

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]

More Related Contents:

  1. How to simulate UNPIVOT in Access?
  2. Achieving ROW_NUMBER / PARTITION BY in MS Access
  3. CONCAT equivalent in MS Access
  4. sql in ms-access [closed]
  5. Managing and debugging SQL queries in MS Access
  6. Upserting in MS-access
  7. How do I write a full outer join query in access
  8. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  9. Table-less UNION query in MS Access (Jet/ACE)
  10. SQL Query – Using Order By in UNION
  11. Oracle SQL – How to Retrieve highest 5 values of a column [duplicate]
  12. Ms Access Query: Concatenating Rows through a query
  13. How to find the employee with the second highest salary?
  14. Access-SQL: Inner Join with multiple tables
  15. Address standardization within a database
  16. Does MS Access support “CASE WHEN” clause if connect with ODBC?
  17. Why does a LIKE query in Access not return any records?
  18. Query for getting value from another record in same table and filter by difference greater than a gap threshold
  19. SQL Updating from an inner join
  20. Lightweight SQL database which doesn’t require installation [closed]
  21. Convert String to Date in MS Access Query
  22. HOW TO SELECT PREVIOUS ROW VALUE? [duplicate]
  23. MSAccess – query to return result set of earliest rows with a unique combination of 2 columns
  24. Editing Record issues in Access / SQL (Write Conflict)
  25. How do I create a table alias in MySQL
  26. Select query with date condition
  27. Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess
  28. Microsoft Access – Case Query
  29. SQL SET DEFAULT not working in MS Access
  30. Check if access table exists

Leave a Comment