Department Highest Salary


  • 0

    Click here to see the full article post


  • 0
    D

    select t2.Name as Department, t1.Name as Employee, t1.Salary
    from Employee as t1, Department as t2
    where t1.DepartmentId = t2.Id
    and (t1.DepartmentId , t1.Salary) in (
    select DepartmentId , max(salary) as Salary
    from Employee
    group by DepartmentId
    )


  • 0
    G

    Write your MySQL query statement below

    select d.Name as 'Department', e.Name as 'Employee', e.Salary
    from Employee e join Department d on d.Id = e.DepartmentId
    where 1>(
    select count(distinct e2.Salary) from Employee e2
    where e2.Salary > e.Salary and e2.DepartmentId = e.DepartmentId
    )
    ;


  • 0
    L

    SELECT d.Name AS Department, e.Name AS Employee, e.Salary AS Salary
    FROM
    Employee e,
    Department d,
    (SELECT MAX(Salary) AS max_sal_in_dept, DepartmentId FROM Employee GROUP BY DepartmentId) ds
    WHERE
    e.DepartmentId = d.Id AND
    e.DepartmentId = ds.DepartmentId AND
    e.Salary = ds.max_sal_in_dept


  • 0

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

    why this does work?

    while this works:
    select E.Name, E.Salary, D.Name from Employee E join Department D on E.DepartmentId = D.Id where (E.Salary, E.DepartmentId) in (select max(Salary), DepartmentId from Employee group by DepartmentId)


  • 0
    F

    @glad2be Hey, your solution gives back correct answer but could you explain the
    where 1>(
    select count(distinct e2.Salary) from Employee e2
    where e2.Salary > e.Salary and e2.DepartmentId = e.DepartmentId
    )
    part? It really confused me, really thanks.


  • 0
    C
    select d.Name as `Department`, e.Name as `Employee`, Salary 
    from (
        select a.Name, a.Salary, a.DepartmentId from Employee as a 
        left join Employee as b 
        on a.DepartmentId = b.DepartmentId 
            and a.Salary < b.Salary
        where b.Salary is NULL) as e
    left join Department as d on DepartmentId = Id
    where d.Name is not NULL; # take care of employees not assigned to a department
    

  • 0
    C

    @xuan_huang1226-sina.com It would be unsafe to include employee name anywhere in the query conditions as it is possible that multiple employee share the same highest salary. The correct one only compares Salary and DepartmentId, so if there exists more than one employee with that max(Salary), the row will be selected.

    The first one works with the example schema, but is an incorrect solution.


  • 0
    C

    @FreyaJcY when count(distinct e2.Salary) evaluates to zero (less than 1 as written), there's no other row of the same DepartmentId with a greater Salary than the row of the outer SELECT.

    It's like:

    foreach row as r in Employee:
      countGreaterSalary=0
      foreach row as o in Employee where o.DepartmentId=r.DepartmentId:
        if o.Salary>r.Salary: countGreaterSalary+=1
      if countGreaterSalary<1: select r
    

    Some nasty looking pseudocode but I guess you will get it.


  • 0
    F

    @catcarbon Thanks! Clear explanation, much clearer now :P


  • 0
    P

    Select D.Name as Department, E1.Name as Employee, Max(E1.Salary) as Salary
    from Employee E1
    join Department D
    on E1.DepartmentId=D.Id
    where E1.Salary=(Select max(Salary) from Employee e where e.DepartmentId=D.Id)
    Group By Department

    Why wouldn't this return the correct employee name?


  • 0
    P

    Select D.Name as Department, E1.Name as Employee, Max(E1.Salary) as Salary
    from Employee E1
    join Department D
    on E1.DepartmentId=D.Id
    where E1.Salary=(Select max(Salary) from Employee e where e.DepartmentId=D.Id)
    Group By Department

    Why wouldn't this return the correct employee name?


  • 0
    I

    @patelmohitj91 Instead of Max(E1.Salary) -- in the first line-- use only E1.Salary
    and also don't use group by at the end, it'll alter the output sequence


  • 0
    P

    @Ishir98 Thanks!


  • 0
    J

    Solution without subqueries.

    SELECT Department.Name as Department, Employee.Name as Employee, Employee.Salary FROM Employee
    LEFT JOIN Employee as tmp ON Employee.Salary < tmp.Salary AND Employee.DepartmentId = tmp.DepartmentId 
    JOIN Department ON Employee.DepartmentId = Department.Id
    WHERE tmp.Salary IS NULL
    

  • 0
    A

    select d.Name as Department,e.Name as Employee,e.Salary from Employee e,
    (select max(Salary) as maxi,DepartmentId from Employee group by DepartmentId) as t ,Department as d
    where e.DepartmentId=t.DepartmentId and t.DepartmentId=d.Id and e.Salary=t.maxi;


  • 0
    X

    SELECT Department.Name AS 'Department',Employee.Name AS 'Employee',MAX(Employee.Salary) AS Salary
    FROM Employee LEFT JOIN Department
    ON Employee.DepartmentId = Department.Id
    GROUP BY Department.Name


Log in to reply
 

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