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?