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