All Forums Database
Santanu84 122 posts Joined 04/13
31 Dec 2014
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

Raja_KT 1246 posts Joined 07/09
31 Dec 2014

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
01 Jan 2015

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

 

Dieter

Santanu84 122 posts Joined 04/13
01 Jan 2015

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

07 Jan 2015

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

 

"Suno Dil Ki Awaz"

09 Jan 2015

 

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)

 

 

Teradata_SA 24 posts Joined 02/14
12 Jan 2015

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

Santanu84 122 posts Joined 04/13
20 Feb 2015

Thanks guys for your response.
Thanking You
Santanu

You must sign in to leave a comment.