Retrieve the name of employee who is getting higher salary from each department

Question

I have three different tables like department, employee and salary for SQL Server.

Its structure

CREATE TABLE department (DeptId INT,DeptName Varchar(100)) 
INSERT INTO department VALUES (1,'Accounts'); 
INSERT INTO department VALUES (2,'Package'); 

CREATE TABLE employee (EmpId INT,DeptId INT,EmpName varchar(20)) 
INSERT INTO employee VALUES (1,1,'Sachin'); 
INSERT INTO employee VALUES (2,1,'Vikas'); 
INSERT INTO employee VALUES (3,2,'Sikha'); 
INSERT INTO employee VALUES (4,2,'Disha'); 

CREATE TABLE salary(EmpId INT,Sal int) 
INSERT INTO salary VALUES (1,400); 
INSERT INTO salary VALUES (2,700); 
INSERT INTO salary VALUES (3,700); 
INSERT INTO salary VALUES (4,900);

Result will be:

DepName  | EmpName
-----------------
Accounts | Vikas
Package  | Disha

Please help me on this, I need a query to find the required result.

I have tried below query, but not able get EmpName.

select DeptName, max(Sal) as Salary from 
(select dep.DeptName, emp.EmpName, sal.Sal from salary as sal
inner join employee emp on emp.EmpId = sal.EmpId 
inner join department dep on dep.DeptId = emp.DeptId) as tbls 
group by DeptName

Can you include your current query and also point out where you are stuck? — Tim Biegeleisen, Jun 30 '21 at 06:11
select DeptName, max(Sal) as Salary from (select dep.DeptName, emp.EmpName, sal.Sal from salary as sal inner join employee emp on emp.EmpId = sal.EmpId inner join department dep on dep.DeptId = emp.DeptId) as tbls group by DeptName ----not able get EmpName — Rakesh, Jun 30 '21 at 06:13
I have already mentioned above comment that what I have tried and where stucked as Tim asked. — Rakesh, Jun 30 '21 at 06:48
You need to [edit] such clarifications into the question, not in a hard to read comment. — Dale K, Jun 30 '21 at 07:09
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Jun 30 '21 at 11:11

score 2 · Accepted Answer · answered Jun 30 '21 at 06:27

2

You can use RANK() to query the salary.

Here is example:

select DeptName
      ,EmpName 
from 
(
   select  DeptName
          ,EmpName
          ,rank() over (partition by d.deptid order by sal desc) as ranknum
   from employee as e
   inner join salary as s
   on e.EmpId=s.EmpId
   left join department as d
   on d.DeptId = e.DeptId
) as ranktable
where ranknum = 1

answered Jun 30 '21 at 06:27

GGG

486
4
9

@Rakesh, please, as this solution worked, mark it as valid :-) – Marcus Vinicius Pompeu Jun 30 '21 at 07:00

score 1 · Answer 2 · answered Jun 30 '21 at 06:34

We can use CTE as well:

;WITH maxSalStaff AS (
    SELECT 
        rnk = ROW_NUMBER() OVER (PARTITION BY d.DeptId ORDER BY s.Sal DESC),
        d.DeptName, 
        e.EmpName,
        s.Sal
        --ItemID
    FROM department d
    INNER JOIN employee e ON e.DeptId = d.DeptId
    INNER JOIN salary s ON s.EmpId = e.EmpId
)
SELECT * FROM maxSalStaff WHERE rnk = 1

score 1 · Answer 3 · answered Jun 30 '21 at 06:41

try this

SELECT T.EmpName, D.DeptName
FROM
(
    SELECT E.EmpId, E.DeptId, E.EmpName, S.Sal, 
    RANK() OVER (PARTITION BY E.DeptId ORDER BY S.Sal DESC)Rank
    FROM employee E
    INNER JOIN salary s ON E.EmpId = S.EmpId
)T 
INNER JOIN department D ON (T.DeptId = D.DeptId)
WHERE T.Rank=1

Nagib Mahfuz · Answer 4 · 2021-06-30T06:55:27.470

0

Use DENSE_RANK() in case multiple people can have same salary. Like below

WITH cteRowNum AS (
    select 
    e.EmpName,
    d.DeptName,
    sal,
    DENSE_RANK() OVER (PARTITION BY DeptName order by sal desc) as RowNo
    from employee e inner join
    departments d on d.DeptId= e.DeptId inner join
    salary s on e.EmpId= s.EmpId
    )
SELECT EmpName, DeptName, Sal
FROM cteRowNum
WHERE RowNo = 1;

edited Jun 30 '21 at 06:55

answered Jun 30 '21 at 06:49

Nagib Mahfuz

833
10
19

Retrieve the name of employee who is getting higher salary from each department

4 Answers4