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?