Find Second Highest Salary - SQL

Database
Enthusiast

Find Second Highest Salary - SQL

Hello Experts

New year greetings. I need another help. Creating a seperate post for that.

EMP

--------

ID NAME SAL DEPT

1 SAM 50 D1

2 TOM 50 D1

3 RAM 40 D1

4 PAM 30 D1

5 JAM 30 D1

6 MAM 40 D2

Find employees with second highest salary in each department, how to achieve this only using SQL ? (not to use dense_rank)

Thanking You

Santanu

7 REPLIES
Enthusiast

Re: Find Second Highest Salary - SQL

can you try something like this?

select id,name,sal,dept from 

(select  id,name,sal,dept,row_number()over(partition by dept,sal order by dept,sal desc) rn from your_table) abc

qualify sum(case when rn=1 then 1 else 0 end) over

(partition by dept order by dept,sal desc rows unbounded preceding) =2

Junior Contributor

Re: Find Second Highest Salary - SQL

Hi Santanu,

if you want to avoid DENSE_RANK your not on TD14.10?

There are several ways to calculate a DENSE_RANK, all of them need nested OLAP-functions:

Missing Functions: DENSE_RANK

For your specific case you can also utilize two RANKs:

SELECT *
FROM
(
SELECT *
FROM EMP
QUALIFY RANK() OVER (PARTITION BY DEPT ORDER BY SAL DESC) > 1
) AS dt
QUALIFY RANK() OVER (PARTITION BY DEPT ORDER BY SAL DESC) = 1
Enthusiast

Re: Find Second Highest Salary - SQL

Thanks Raja and Dnoeth for your responses. It was really helpful.

Thanking You

Santanu

Re: Find Second Highest Salary - SQL

SELECT *

FROM

 (

   SELECT *

   FROM EMP

   QUALIFY RANK() OVER (PARTITION BY DEPT ORDER BY SAL DESC)  > 1

 ) AS dt

QUALIFY RANK() OVER (PARTITION BY DEPT ORDER BY SAL DESC)  = 2

Enthusiast

Re: Find Second Highest Salary - SQL


create table retail.Find_nth_sal
(
id int,
Name varchar(20),
sal int,
Dept Char(2)
);

insert into retail.Find_nth_sal (1,'SAM ',50,'D1');
insert into retail.Find_nth_sal (2,'TOM ',50,'D1');
insert into retail.Find_nth_sal (3,'RAM ',40,'D1');
insert into retail.Find_nth_sal (4,'PAM ',30,'D1');
insert into retail.Find_nth_sal (5,'JAM ',40,'D1');
insert into retail.Find_nth_sal (6,'MAM ',30,'D2');

select * from retail.Find_nth_sal t1
where 2= (select count(distinct sal) from retail.Find_nth_sal t2 where t2.sal >= t1.sal and t1.dept = t2.dept)
Enthusiast

Re: Find Second Highest Salary - SQL

QUALIFY ROW_NUMBER() OVER (PARTITION BY DEPT ORDER BY SAL DESC) = 2

Enthusiast

Re: Find Second Highest Salary - SQL

Thanks guys for your response.

Thanking You

Santanu