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)
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?
select d.Name as Department , tmp2.name 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 ?
it should be "where tmp.maxsalary = e.Salary and tmp.DepartmentId = e.DepartmentId"
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)
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?
@tuhu Try this!
select tmp.Department, Employee.Name as Employee, tmp.Salary from Employee, (select Department.name 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
Pls check out my solution without subquery, max function and follow-up questions:)
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.