GROUP BY HAVING not working for multiple highest salary, why?


  • 11
    L

    SELECT b.Name as Department, a.Name as Employee, a.Salary FROM Employee a JOIN Department b ON a.DepartmentId = b.Id GROUP BY Department HAVING a.Salary = max(a.Salary)

    This way it was not able to return multiple rows with same highest salary. I can't figure why, please help!


  • 0
    R

    I have the exact same problem and my query is very similar to yours

    select Department.Name as Department, Employee.Name as Employee, Employee.Salary as Salary
    from Employee right join Department on Employee.DepartmentId = Department.Id
    group by Department.Id having Employee.salary = max(Employee.salary)
    

    After checking the correct solutions posted by other people. I think there might be something to do with the group by? It seems to me it only produces one record for each group.

    To overcome this issue, we have to join the result table with the original table again.
    Here's the final query:

    select tmp.Department as Department, e1.Name as Employee, e1.Salary as Salary from Employee e1 right join (
        select d.Id as Id, d. Name as Department, max(e.salary) as Salary from Employee e right join Department d on e.DepartmentId = d.Id
        group by d.Id having Salary = max(e.salary) ) tmp
    on e1.Salary = tmp.Salary and e1.DepartmentId = tmp.Id
    

    The rows in the tmp table is the ones you generated I just included Id field into the tmp table for the first right join


  • 1
    F

  • 0
    S

    @FengLi666 said in GROUP BY HAVING not working for multiple highest salary, why?:

    in

    select d.Name as Department, e.Name as Employee, Salary
    from Department as d join Employee as e on d.Id = e.DepartmentId
    where (e.DepartmentId, Salary) in (select DepartmentId, max(Salary)
                      from Employee
                      group by DepartmentId)
    

  • 2
    L

    For the example dataset,

    +----+-------+--------+--------------+
    | Id | Name  | Salary | DepartmentId |
    +----+-------+--------+--------------+
    | 1  | Joe   | 70000  | 1            |
    | 2  | Henry | 80000  | 2            |
    | 3  | Sam   | 60000  | 2            |
    | 4  | Max   | 90000  | 1            |
    +----+-------+--------+--------------+
    

    If you run the following query you will get the first row ["Joe", 70000]. The reason is that MySQL returns the first row in each department that meets the HAVING clause condition.

    SELECT      Name, Salary
    FROM        Employee
    GROUP BY    DepartmentId
    HAVING      MAX(Salary) = 90000
    ;
    

    When you run the next query, it only returns the record ["Henry", 80000]. The reason is that for department 2, the first record is for "Henry" and his salary 8000 matches the department's max salary 8000. For department1, however, the first record is for "Joe" as shown above and his salary 7000 does not match the department's max salary 9000.

    SELECT      Name, Salary
    FROM        Employee
    GROUP BY    DepartmentId
    HAVING      MAX(Salary) = Salary
    ;
    

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.