MS Access Select top n query grouped by multiple fields

ms-access, sql

Solution

You can do something like this

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 |

Here is SQLFiddle demo. Demo as usually is for SQL Server but expected to work in MS Access, maybe with minor syntactic tweaks

Problem

This is part 2 of a problem that was already answered by peterm on this board. Thanks again peterm! So I have code that will return the top 3 test scores for a given student. My table looks like the following: 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 Thanks to peterm, I have the following code which will do this for me: ``` SELECT StudentID, TestID, TestScore FROM MyTable t WHERE TestID IN ( SELECT TOP 3 TestID FROM MyTable WHERE StudentID = t.StudentID ORDER BY TestScore DESC, TestID ) ORDER BY StudentID, TestScore DESC, TestID; ``` My new problem is now I need to add two new fields to the table for Subject and Year, so I need to find the top 3 scores for each Subject-Student-Year combination. Once I have the top 3 scores for each combination, I need to average them so that I will have one averaged score of the top 3 scores for each student-subject-year combination. Hopefully, I've explained this clearly enough without having to mock up another table. Thanks in advance.

Original source