Group by Expression

Vote count:

0

Grades:

SID CID GRADE
--------------
S1  C1  50
S1  C2  85
S1  C3  60
S1  C4  90
S1  C5  50
S2  C1  30
S2  C2  40
S3  C2  85
S4  C2  80
S4  C4  75
S4  C5  60

We have Grades table, SID = Student_ID CID = Course_ID

We want to get SID of student who gets the highest grade from the course ‘C1’.

This is my solution

SELECT DISTINCT SID
FROM GRADES
WHERE GRADE =
    (SELECT max(GRADE)
     FROM GRADES
     GROUP BY CID HAVING CID = 'C1')

it works wrong in my opinion, how can i fix it?

asked 1 min ago

DjMix

8