Reputation: 3082
I have a table Employee with fields dept, employee ans salary. I want a query to list Department wise highest salaries and name of the employee with that salary.
I know it is simple. I googled but found answers like this, which lists only the department and salary
SELECT dept, SUM (salary)
FROM employee
GROUP BY dept;
Upvotes: 2
Views: 7977
Reputation: 1
select * from (select salary,last_name,dense_rank()
over (order by salary desc)
sal_rank from employees) where sal_rank <=3;
That's all... this is the output below:
SALARY LAST_NAME SAL_RANK
---------- ------------------------- ----------
24000 King 1
17000 Kochhar 2
17000 De Haan 2
14000 Russell 3
Upvotes: -1
Reputation: 46
This will do it.
SELECT E1.DEPT, E2.ENAME, E1.HIGHEST_SALARY
FROM
(SELECT DEPT, MAX(SALARY) HIGHEST_SALARY
FROM EMPLOYEE
GROUP BY DEPT) E1
INNER JOIN EMPLOYEE E2 ON E1.HIGHEST_SALARY = E2.SALARY
AND E1.DEPT = E2.DEPT
Upvotes: 3
Reputation: 8806
SELECT e.*, d.deptname
FROM employee e
JOIN department d ON e.deptid = d.deptid
WHERE EXISTS (SELECT 1
FROM employee e_in
JOIN department d_in ON e_in.deptid = d_in.deptid
WHERE d_in.deptid = d.deptid
GROUP BY d_in.deptid
HAVING MAX(e_in.salary) = e.salary)
Upvotes: 3
Reputation: 9576
SELECT e1.*
FROM employee e1
JOIN (SELECT dept, MAX(salary) FROM employee GROUP BY dept) e2 ON
e1.dept = e2.dept AND e1.salary = e2.salary
Upvotes: 4
Reputation: 263943
SQL Server 2008 supports Window Functions
which help you get what you want.
WITH recordList
AS
(
SELECT dept, employeeName, salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) rn
FROM employee
)
SELECT dept, employeeName, salary
FROM recordList
WHERE rn = 1
Upvotes: 3