Write a query to find the second highest salary of each department

Data

id	name	salary	department
1	Gaurav	50200	Account
2	Pyush	10960	HR
3	Ram	70870	Secutity
4	Lakshaman	8600	HR
5	Saurab	591000	Account
6	Dev	126500	Secutity
7	Nishant	935200	HR
8	Rahul	540200	Account
9	Ayush	238200	Secutity
10	Saksham	7415200	Account

Here we are having a table players having the years and players name.

So, we need to write a query to find number of players who won only the gold medals

(Hint: The players who won others medals too with gold medals should be excluded)

You can create the table at your end using the statements below:

create table Employees(id int, name varchar(20), salary varchar(50), department varchar(50));
insert into Employees values(1, 'Gaurav', '50200', 'Account');
insert into Employees values(2, 'Pyush', '10960', 'HR');
insert into Employees values(3, 'Ram', '70870', 'Secutity');
insert into Employees values(4, 'Lakshaman', '8600', 'HR');
insert into Employees values(5, 'Saurab', '591000', 'Account');
insert into Employees values(6, 'Dev', '126500', 'Secutity');
insert into Employees values(7, 'Nishant', '935200', 'HR');
insert into Employees values(8, 'Rahul', '540200', 'Account');
insert into Employees values(9, 'Ayush', '238200', 'Secutity');
insert into Employees values(10, 'Saksham', '7415200', 'Account');

Solution:

Method 1 (Using RANK() function):

SELECT name, salary, department FROM
(SELECT *,
rank() OVER (partition by department ORDER BY salary desc) AS rn
FROM Employees) a where rn = 2;

Method 2 (Using DENSE-RANK() Function):

SELECT name, salary, department FROM
(SELECT *,
DENSE_RANK() OVER (partition by department ORDER BY salary desc) AS rn
FROM Employees) a where rn = 2;

Method 3 (Using CTE):

WITH CTE AS
 (
 SELECT name, salary, department dept,
  RANK() over( PARTITION BY dept ORDER BY salary ) AS rn
 FROM employees) 
 SELECT name, salary, dept FROM CTE
 WHERE rn = 2;

The output of all above queries:

Data

name	salary	department
Saurab	591000	Account
Lakshaman	8600	HR
Ayush	238200	Secutity

Recent Posts