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

maxMySQL

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?

Best Answer

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.

Related Solutions

MySQL Error 1241 – Operand Should Contain 1 Column, INTERSECT Two Subqueries

You probably want to replace:

WHERE cTitle='Database Management Systems'
IN (
SELECT S2.StuName, S2.StuID 
...
  ) ;

with:

WHERE cTitle='Database Management Systems'
  AND (S.StuName, S.StuID) IN (                -- this changed
SELECT S2.StuName, S2.StuID 
...
  ) ;

Mysql – Retrieving specific results from a query

Allow me to start with a review of your table design decisions (or of those that preceded you) that directly led you to this problem, and how to avoid it. If you do not want to read it, go directly to the end, where I suggest on an edit the actual solution.

You have a single table that is crammed by several design entities. While this is not bad per se, you must be aware that you are denormalizing your table design, and that can lead to problems.

I see 3 concepts on the same table:

Employee intrinsic details (First name, Family name)
Department membership
Job position holding by the employee within the department

According to your description, an employee can be part of several departments (and vice-versa) and you are implementing that as a bit field. I find interesting that you have chosen a binary data type, when a bit or a set data types would be potentially smaller and more legible respectively. However, those data types are usually avoided, not only because they are not very standard, but because they are used to denormalize columns.

Assuming there is a departments table, a better option would be to create a separte table: DepartmentEmployees, like this

Employees Table
===============
EmployeeId Int Primary Key auto_increment
Firstname  Varchar(40)
Surname    Varchar(40)
JobCode    Char(1)

DepartmentEmployees
===================
EmployeeId Int
DepartmentId Int
Primary Key(EmployeeID, DepartmentId)

To query all employees from given deparment "?":

SELECT E.*
FROM Employees E
JOIN DepartmentEmployees DE
ON E.Id = DE.EmployeeID
WHERE DepartmentId = ?

Now let's have a look at JobCode. You have to ask yourself if a Job Position is a property of the employee or of the employee within a department. If the latter, please consider adding that property there:

Employees Table
===============
EmployeeId Int Primary Key auto_increment
Firstname  Varchar(40)
Surname    Varchar(40)

DepartmentEmployees
===================
EmployeeId Int
DepartmentId Int
JobCode Char(1)
Primary Key(EmployeeID, DepartmentId)

To query all employees from given deparment "?", with its Job Code:

SELECT E.*, DE.JobCode
FROM Employees E
JOIN DepartmentEmployees DE
ON E.Id = DE.EmployeeID
WHERE DepartmentId = ?

This way you can get the JobCode, which can be different for each employee-department tuple (if needed).

You can consider creating (if they do not exist already), separate tables for Department, Jobs (as in, positions, for JobCode) and add unique Primary key everywhere.

In general, creating an intermediate table whenever there is an N:N relationship is a good option, unless there is a reason to denormalize the relationship (leading to more complex code).

Edit: The problem you are facing is horrible from all points- aside from the normalization problems, adding one extra record makes no sense. But here is the wanted solution:

Knowing there is nothing you can do about it, you are lucky because it is a very common query- that not only has a name "GROUPWISE MAXIMUM", it even has its own page on the MySQL manual: http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html

There is both a subquery and a JOIN solution, I usually like the JOIN solution more, it would go something like this:

SELECT E1.FirstName, E1.Surname, E1.Department, s1.JobCode
FROM Employees E1
LEFT JOIN Employees E2
ON E1.FirstName = E2.FirstName AND E1.Surname = E2.Surname -- use an id for the SELF JOIN if possible, whatever is unique
AND E1.JobCode < E2.JobCode -- change the direction of the comparison depending if you want the lowest or the highest JobCode
WHERE E2.FirstName IS NULL  -- again, use the id if available
  AND E1.Department = ...;  -- or whatever the department filter should look like in your current setup

Allow me to shower after writing such a solution.

Best Answer

Related Solutions

MySQL Error 1241 – Operand Should Contain 1 Column, INTERSECT Two Subqueries

Mysql – Retrieving specific results from a query

Related Question