1

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?

1

2 Answers 2

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.

3
  • What should the ON clause (s.whatever=m.whatever) refer to?
    – user78192
    Commented Oct 14, 2015 at 14:30
  • Fixed this in the answer.
    – tombom
    Commented Oct 14, 2015 at 14:51
  • I entered the following into MYSQL 5.6: 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); This is the error I received: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON S.STUID=M.STUID WHERE S.GPA=(SELECT MAX(S2.GPA) FROM STUDENTS S2 WHERE S2.STU' at line 1
    – user78192
    Commented Oct 14, 2015 at 14:59
0

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;
1
  • This query works but I'm not getting the highest GPA. It is matching stuNames with the correct corresponding department and GPA. However the students listed do not have the highest GPA in their department according to my Students table.
    – user78192
    Commented Oct 14, 2015 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.