How can I add an additional field to an SQL Group By clause

Question

I have the following MS-Access SQL Table:-

NAME, SUBJECT, SCORE

..and I need to find the average score, highest score and the subject that the highest score was achieved against. I have managed to produce the following query but not sure how I can incorporate the SUBJECT field:-

SELECT  NAME,  Avg(SCORE) AS AverageScore, MAX(SCORE) AS best_score
FROM Scoretable
GROUP BY NAME

Any ideas ?

Answer 1

select sm.NAME, sm.AverageScore, sm.best_score, s.SUBJECT
from (
    SELECT  NAME,  Avg(SCORE) AS AverageScore, MAX(SCORE) AS best_score 
    FROM Scoretable 
    GROUP BY NAME 
) sm
inner join Scoretable s on sm.NAME = s.NAME 
    and sm.best_score  = s.SCORE