Going fast! #DataquestChallenge Premium Annual Offer:
500 get 50% & the next 1000 get 40% off.
GET OFFER CODE

MYSQL: Highest salary problem

I am working on this problem

my solution:

*select Department.Name as “Department”, Employee.name as “Employee” , max(Salary) as “Salary” *
from Employee inner join Department on Department.Id=Employee.DepartmentId
group by Department.Id;

Any idea why this code is giving wrong output?

After joining the tables, when you use GROUP BY it will only output one of the rows corresponding to Department.Id.

So, if the table consisted of -

Department Id Department Name Employee Salary
1 “IT” “Joe” 70000
1 “IT” “Jim” 90000
2 “Sales” “Henry” 80000

If you group the above by the Id, you would get -

Department Id Department Name Employee Salary
1 “IT” “Joe” 70000
2 “Sales” “Henry” 80000

You will only get one of the rows corresponding to the Id = 1 as you can see above. So, taking the maximum salary will always only return that one row.

You need to re-think how you can get the maximum salary in each department without grouping as above.

One way could be (I haven’t tried it, so no guarantee) to think of this in terms of two temporary tables. One in which you have the highest salary per department, and one in which you have the entire data from the joined tables. Then use the former to filter the latter.