Simple solution, easy to understand

  • 26
    SELECT dep.Name as Department, emp.Name as Employee, emp.Salary 
    from Department dep, Employee emp 
    where emp.DepartmentId=dep.Id 
    and emp.Salary=(Select max(Salary) from Employee e2 where e2.DepartmentId=dep.Id)

  • 0

    I am a new developer in mysql and i couldn't understand as to how have u done without a group by.Could you please explain the query?

  • 0
    select d.Name as Department , as Employee, tmp2.Salary as Salary  
    from Department d,
    (select e.Name, e.Salary, e.DepartmentId from Employee e,
        (select DepartmentId,Max(Salary) as maxsalary from Employee group by DepartmentId) tmp 
    where tmp.maxsalary = e.Salary ) tmp2  
    where d.Id = tmp2.DepartmentId;

    Althought a little bit difficult, it can pass in my local env. got error in leetcode , why ?

  • 0

    without groupby, it just means it compares all the rows in the table

  • 0
    This post is deleted!

  • 0
    This post is deleted!

  • 0

    it should be "where tmp.maxsalary = e.Salary and tmp.DepartmentId = e.DepartmentId"

  • 0

    This solution turns out to be slower than mine.
    The primary reason is because in the last line of SQL,
    you run the subquery every time you take a new row out from (dep join emp)

    select d.Name Department, e.Name Employee, e.Salary
    from Employee e inner join Department d
    on e.DepartmentId = d.Id
    inner join (select MAX(Salary) Salary, DepartmentId
    from Employee
    group by DepartmentId
    ) t
    on e.Salary = t.Salary
    where e.DepartmentId = t.DepartmentId;

  • 0

    I am having a hard time understanding everything after AND. How does that line allow us to get the highest salary value from each department?

  • 0
    This post is deleted!

  • 0

    @tuhu Try this!

    select tmp.Department, Employee.Name as Employee, tmp.Salary
    from Employee,
    (select as Department, Department.ID as did, max(Employee.Salary) as Salary
    from Department, Employee
    where Department.ID = Employee.DepartmentId 
    group by Department.ID) tmp
    where Employee.DepartmentId = tmp.did and Employee.Salary = tmp.Salary

  • 0

    Pls check out my solution without subquery, max function and follow-up questions:)

Log in to reply

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