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..
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;
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;
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;
19 People are following this question.
