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

Tags (3)
8 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 dtQUALIFY 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

Enthusiast

Re: Find Second Highest Salary - SQL

When i try this, it brings back no result for me

`SELECT *   FROM EMP   QUALIFY DENSE_RANK() OVER (PARTITION BY DEPT ORDER BY SAL DESC)  = 2`

But when i try this, it gives me the record with dense_rank=2

`SELECT *   FROM EMP   QUALIFY DENSE_RANK() OVER (PARTITION BY DEPT ORDER BY SAL DESC)  > 1`

I dont get it, why would the 1st one not work.