SQL MAX of column including its primary key

I have the following table:

StudentID SemesterID Grade
1 1 85
2 1 90
3 1 84
3 2 94
1 2 85
2 2 96

I use the following query to return the MAX grade for each semester

select SemesterID, max(Grade) from mytable
group by SemesterID

SemesterID max(Grade)
1 90
2 94

I want to also display the StudentID for the corresponding row as follows:

SemesterID max(Grade) StudentID
1 90 2
2 94 3

Could you please help me write the proper SQL statement?

Perfect. Thank you very much!

this is a pretty common problem and there is an easy way to solve it

take the query you wrote –

select SemesterID, max(Grade) from mytable
group by SemesterID

and use it as a subquery, to join it back to the table, like this –

SELECT t.SemesterID
     , t.Grade
     , t.StudentID
  FROM ( [COLOR="Blue"]select SemesterID
              , max(Grade) AS max_grade
           from mytable
         group 
             by SemesterID [/COLOR]) AS m
INNER
  JOIN mytable AS t
    ON t.SemesterID = m.SemesterID
   AND t.Grade = m.max_grade

:cool: