CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008greatest-n-per-group
techBeginner
techBeginner

Reputation: 3850

How to get the highest paid employee row

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

Answers (6)

Chandan Kumar
Chandan Kumar

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

Raju
Raju

Reputation: 11

select * from employee 
where salary in
(select  max(salary) from employee group by dept);

Upvotes: 1

Ben Thul
Ben Thul

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

Mikael Eriksson
Mikael Eriksson

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

Derek
Derek

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

PaulStock
PaulStock

Reputation: 11283

Something like this?

select * from employee where salary = (select max(salary) from employee)

Upvotes: 3

Related Questions