Find Max(value) for each department using GROUP BY MYSQL

Question

I want to print the name of student in each department who has the highest gpa,

Students(stuID: String, stuName: String, gender: String, 
        birthdate: Date, enterYear: Year, gpa: Float)
Departments(deptName: String, numPhDs: Integer)
Majors(deptName: String, stuID: String, degreeProgram: String, 
        attendYear: Year, attendSemester: String)

My query looks like

SELECT S.StuName, M.DEPTNAME
FROM Students S NATURAL JOIN MAJORS M
WHERE S.GPA=(SELECT MAX(S2.GPA) FROM STUDENTS S2)
GROUP BY DEPTNAME;

I am only getting one student's name and their corresponding department (MATHEMATICS). How can I get the student with the highest GPA for each department? Where did I go wrong on my query?

Answer 1

You're missing the ON clause in your join. The GROUP BY is not necessary, since you're selecting the MAX(GPA) entry for each student anyway. For this, the subquery needs to relate to the outer query.

SELECT S.StuName, M.DEPTNAME
FROM Students S NATURAL JOIN MAJORS M ON S.stuID = M.stuID
WHERE S.GPA=(SELECT MAX(S2.GPA) FROM STUDENTS S2 WHERE S2.StuName = S.StuName)
;

Here's a manual entry regarding your problem, where you can also see other ways of solving this.

Answer 2

You do need the GROUP BY if you want to SELECT the just the students with the highest GPA in each department. I added GPA to the statement for clarity.

SELECT S.StuName, S.gpa, M.DeptName
FROM Students S 
JOIN Majors M ON S.StuID = M.StuID
WHERE S.gpa=(SELECT MAX(S2.gpa) FROM Students S2 WHERE S2.StuName = S.StuName) GROUP BY DeptName;