question

xnykhizer avatar image
xnykhizer asked

Trying to get highest paid employee in departmen 'd01'

---------- Trying to get highest paid employee in departmen 'd01' ------------- hello, can any one tell me why i cannot get any results back please? select emp.FName,emp.Sal from ( select emp.empid, emp.DeptID, emp.Sal from Emp where emp.deptid = 'D01')a, Emp where a.EmpID = emp.EmpID AND emp.Sal = (select MAX(emp.sal) from Emp) any help would be appreciated. thank you much..
sql-server-2008sqlquerysub-querymax
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
Basically, you're joining people in D01 with the highest salary of all, which will only work if the highest paid employee IS in D01. Strip it back to a simpler query like this: select top 1 emp.FName,emp.Sal from Emp where deptid = 'D01' order by Sal desc;
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I started to do that, but got bored ;-)
1 Like 1 ·
sp_lock avatar image
sp_lock answered
CTE option. Will return one per depatment WITH topPaid AS ( SELECT emp.FName , emp.Sal , RANK() OVER ( PARTITION BY emp.deptid ORDER BY emp.sal DESC ) AS [r] FROM employees emp ) SELECT * FROM topPaid WHERE [r] = 1; You may have multiple employees on the same salary so you could test this... SELECT emp.Fname, emp.sal FROM Employee emp INNER JOIN ( SELECT ie,deptid , MAX(ie.sal) ms FROM Employee ie GROUP BY ie.deptid ) es ON emp.dept = es.dept AND emp.salary = es.ms;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

xnykhizer avatar image
xnykhizer answered
Below: this bring back all the employees from department 'd01' SELECT emp.Fname, emp.sal FROM Employee emp INNER JOIN ( SELECT ie,deptid , MAX(ie.sal) ms FROM Employee ie GROUP BY ie.deptid ) es ON emp.dept = es.dept AND emp.salary = es.ms; Below: this one gave me the right result but what if the department 'd01' have multiple employees who has the same max salary? select top 1 emp.FName,emp.Sal from Emp where deptid = 'D01' order by Sal desc;
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do you have a question? If you added a WHERE clause of Deptid = 'D01' it will return just that department
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.