How to Find the Second or nth Highest Salary Department Wise in SQL Server

Tutorialsrack 09/12/2020 SQL SQL Server

In this article, you will learn how to find the second or Nth highest salary department wise in SQL Server. There are many ways to find the 2nd or Nh highest salary department wise in SQL Server.

Here are some examples to get or find the 2nd or Nth highest salary for each department in SQL Server.

Find the 2nd or third or Nth highest Salary in SQL Server

Find the 2nd or third or Nth highest Salary in Linq C#

Dummy Data for Find the second Highest Salary for each Department

Table: Employee Table(tbl_emp)

id	firstname	lastname	gender	salary	city	deptid
1	Tom	Hardy	m	200000	New York	1
2	Hugh	Jackman	m	21500	Los Angles	2
3	Scarlett	johansson	f	15000	Manhattan	1
4	Chris	Hemsworth	m	52000	Sydney	1
6	Harry	Potter	m	545000	New York	3
7	Tom	Holland	m	890000	New York	2
8	Sofia	vergara	f	1500000	Colombia	3

Table 2: Department Table(tbl_department)

deptid	deptName
1	IT
2	HR
3	Account

Example 1: First Approach to Get Second Highest Salary Department Wise

-- First Approach to Get Second Highest Salary Department Wise
select dept.deptName,MAX(salary) as highestSalary from 
(select Emp1.deptid,Emp1.salary 
 from tbl_emp Emp1,(select deptid,MAX(salary) as salary 
                   from tbl_emp group by deptid) Emp2
 where emp1.deptid=Emp2.deptid 
 and Emp1.salary<Emp2.salary) as Employee 
 join tbl_department dept on Employee.deptid=dept.deptid
group by dept.deptName;

Output

deptName highestSalary
---------------- ------------------
Account 545000.00
HR 21500.00
IT 52000.00

Example 2: Second Approach to Get the Second Highest Salary Department Wise

-- Second Approach to Get the Second Highest Salary Department Wise
select dept.deptName, max(Emp1.salary) as highestSalary
from tbl_emp Emp1 join tbl_department dept on Emp1.deptid=dept.deptid
where Emp1.salary < (select max(salary)
                  from tbl_emp Emp2
                  where Emp2.deptid = Emp1.deptid
                 )
group by dept.deptName;

Output

deptName highestSalary
---------------- ------------------
Account 545000.00
HR 21500.00
IT 52000.00

Example 3: Third Approach to Get the Second Highest Salary Department Wise

-- Third Approach to Get the Second Highest Salary Department Wise
SELECT  dept.deptName, Emp1.salary as highestSalary
FROM tbl_emp Emp1 join tbl_department dept on Emp1.deptid=dept.deptid
WHERE 1 = (SELECT COUNT(DISTINCT Emp2.salary)
           FROM tbl_emp Emp2
           WHERE Emp2.salary > Emp1.salary AND Emp1.deptid = Emp2.deptid)
group by dept.deptName,Emp1.salary;

Output

deptName highestSalary
---------------- ------------------
Account 545000.00
HR 21500.00
IT 52000.00

Example 4: Fourth Approach to Get the Second Highest Salary Department Wise

If you want to find the second(2^nd) or third(3^rd) or N^thhighest salary department wise in SQL Server. Then use this approach to get or find the N^thhighest salary department wise.

Example 4: Fourth Approach to Get the Second Highest Salary Department Wise

-- Fourth Approach to Get the Second Highest Salary Department Wise
WITH cteRowNum AS (
SELECT *,
       DENSE_RANK() OVER(PARTITION BY deptId ORDER BY Salary DESC) AS RowNum
    FROM tbl_emp
 )
 SELECT dept.deptName,cte.salary as highestSalary
 FROM cteRowNum cte join tbl_department dept on cte.deptid=dept.deptid
 WHERE RowNum = 2;

Output

deptName highestSalary
----------------- ----------------------
IT 52000.00
HR 21500.00
Account 545000.00

I hope this article will help you to understand how to find the second or Nth highest salary department wise in SQL Server.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Tagged: nth highest salary 2nd or 3rd highest salary Department Wise Nth Highest Salary

How to Find the Second or nth Highest Salary Department Wise in SQL Server

Tutorialsrack 09/12/2020 SQL SQL Server

Example 1: First Approach to Get Second Highest Salary Department Wise

Example 1: First Approach to Get Second Highest Salary Department Wise

Output

Example 2: Second Approach to Get the Second Highest Salary Department Wise

Output

Example 3: Third Approach to Get the Second Highest Salary Department Wise

Output

Example 4: Fourth Approach to Get the Second Highest Salary Department Wise

Example 4: Fourth Approach to Get the Second Highest Salary Department Wise

Output

Related Posts

Comments

Recent Posts

Tags