SQL: finding (multiple) best students of a professor by grade

I have to find the students with the best grade of each professor with this given table “x”:

Prof	Student	Grade
A	1	1.0
A	2	1.0
A	5	5.0
A	6	1.3
B	3	1.2
B	4	2.0
…	…	…

The result should look like this:

Prof	Student	Grade
A	1	1.0
A	2	1.0
B	3	1.2
…	…	…

I already tried grouping the first table by Prof with:

SELECT Prof, Student, MIN(Grade)
FROM x
GROUP BY Prof

But the result gives me only one Student per professor like this:

Prof	Student	Grade
A	1	1.0
B	3	1.2
…	…	…

So student 2 – who is also professor A’s best student – is missing. How do I fix this?

Answer

You ave two possibilities

Once you select the minimum of grades for every professor and use tat as resilset fpr an IN clause.

SELECT Prof, Student, Grade
FROM x
WHERE  (Prof,  Grade) IN (SELECT Prof,  MIN(Grade)
FROM x
GROUP BY Prof)

Or as joined table, which is on big table usually faster

SELECT Prof, Student, Grade
FROM x JOIN
(SELECT Prof,  MIN(Grade) mingrade
FROM x
GROUP BY Prof) t1 x.prof = t1.prof AND x.Grade = t1.mingrade