Reputation: 3850
A table consists of employee name, address, phone, department, salary:
How to get the highest paid employee row from each department?
I tried with
select dept, max(salary) from employee group by dept
but it gives only two columns. But I want to select an entire row. How to do it?
Alternatively how to add more columns to the result?
(I am using SQL Server 2008)
Upvotes: 6
Views: 42229
Reputation: 4638
If situation like you have both employee details and department in same table and you have to find the highest paid employee from each department like given below
EmployeeId EmployeeName Department Salary
1 Neeraj Dot Net 45000
2 Ankit Java 5000
3 Akshay Java 6000
4 Ramesh Dot Net 7600
5 Vikas Java 4000
7 Neha Php 8500
8 Shivika Php 4500
9 Tarun Dot Net 9500
Then you can solve it by using below solutions
Solution - 1
SELECT t.EmployeeName,t.Department,t.Salary
FROM(SELECT MAX(Salary) AS TotalSalary,Department FROM Employee GROUP BY Department)
AS TempNew Inner Join Employee t ON TempNew.Department=t.Department
and TempNew.TotalSalary=t.Salary
ORDER BY t.Department ASC
Solution -2
;WITH EmployeeDetails AS (
SELECT EmployeeName, Department, DENSE_RANK() OVER(PARTITION BY Department
ORDER BY Salary DESC) AS SalaryRank, Salary FROM Employee )
SELECT EmployeeName, Department, Salary FROM EmployeeDetails WHERE SalaryRank=1
OUTPUT
EmployeeName Department Salary
Neeraj Dot Net 45000
Akshay Java 6000
Neha Php 8500
Upvotes: -1
Reputation: 11
select * from employee
where salary in
(select max(salary) from employee group by dept);
Upvotes: 1
Reputation: 32737
with cte as (
select *, rank() over (partition by dept order by salary desc) as [r]
from employees
)
select * from cte where [r] = 1;
Upvotes: 3
Reputation: 139010
SELECT name,
address,
phone,
department,
salary,
dept
FROM (SELECT name,
address,
phone,
department,
salary,
dept,
row_number() OVER(PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employee) AS e
WHERE e.rn = 1
Using row_number() will give you one row if there is a tie for the highest salary. If you want all the highest salaries for each department you should use rank() instead.
SELECT name,
address,
phone,
department,
salary,
dept
FROM (SELECT name,
address,
phone,
department,
salary,
dept,
rank() OVER(PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employee) AS e
WHERE e.rn = 1
Upvotes: 5
Reputation: 23318
You simply need to join the query you currently have back to the employee table to get the full employee information.
select e.*
from employee e
inner join (select dept, max(salary) ms from employee group by dept) m
on e.dept = m.dept and e.salary = m.ms
Upvotes: 14
Reputation: 11283
Something like this?
select * from employee where salary = (select max(salary) from employee)
Upvotes: 3