Skip to content

MS Access Select top n query grouped by multiple fields

SELECT StudentID, Year, Subject,  AVG(TestScore) AS AvgScore
  FROM
(
  SELECT StudentID, Year, Subject, TestScore
   FROM MyTable t
   WHERE TestID IN
  (
   SELECT TOP 3 TestID 
     FROM MyTable
    WHERE StudentID = t.StudentID
      AND Year = t.Year
      AND Subject = t.Subject
    ORDER BY TestScore DESC, TestID
  )
) q
 GROUP BY StudentID, Year, Subject
 ORDER BY StudentID, Year, Subject;

Sample output:

| STUDENTID | YEAR | SUBJECT | AVGSCORE |
|-----------|------|---------|----------|
|         1 | 2012 |       1 |       91 |
|         1 | 2012 |       2 |       84 |
|         2 | 2012 |       1 |       94 |
|         2 | 2012 |       3 |       95 |

Data Source

StudentID, Test ID, Score
1,1, 95
1, 2, 90
1, 3, 90
1, 4, 90
2, 1, 99
2, 2, 95
2, 3, 90
2, 4, 90

https://stackoverflow.com/questions/21059077/ms-access-select-top-n-query-grouped-by-multiple-fields