Getting Nth Maximum

Question

Post reply

Getting Nth Maximum

Easwarapillai

SSC Journeyman

Points: 96
More actions
July 13, 2005 at 6:30 am

#76373

I have a table as below. how can i get the third maximum salary for each department.
create table tmb1 (deptno numeric, sal numeric )
insert into tmb1 (deptno, sal ) values (1, 1000)
insert into tmb1 (deptno, sal ) values (1, 2000)
insert into tmb1 (deptno, sal ) values (1, 2000)
insert into tmb1 (deptno, sal ) values (1, 3000)
insert into tmb1 (deptno, sal ) values (2, 1000)
insert into tmb1 (deptno, sal ) values (2, 1000)
insert into tmb1 (deptno, sal ) values (2, 2000)
insert into tmb1 (deptno, sal ) values (2, 3000)
insert into tmb1 (deptno, sal ) values (3, 1000)
insert into tmb1 (deptno, sal ) values (3, 2000)
insert into tmb1 (deptno, sal ) values (3, 3000)
insert into tmb1 (deptno, sal ) values (3, 4000)

Regards,
Easwar

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Is this the results you need?

create table tmb1 (deptno numeric, sal numeric )

insert into tmb1 (deptno, sal ) values (1, 1000)

insert into tmb1 (deptno, sal ) values (1, 2000)

insert into tmb1 (deptno, sal ) values (1, 3000)

insert into tmb1 (deptno, sal ) values (2, 1000)

insert into tmb1 (deptno, sal ) values (2, 2000)

insert into tmb1 (deptno, sal ) values (2, 3000)

insert into tmb1 (deptno, sal ) values (3, 1000)

insert into tmb1 (deptno, sal ) values (3, 2000)

insert into tmb1 (deptno, sal ) values (3, 3000)

insert into tmb1 (deptno, sal ) values (3, 4000)

Select T1.deptno, Min(sal) as sal_3rd

from tmb1 T1

where exists (Select *

from tmb1 T2

where T1.deptno = T2.deptno and T1.sal = T2.sal

and T2.sal in (Select top 3 Sal

from tmb1 T3

where T3.deptno = T1.deptno

order by Sal Desc))

group by T1.deptno

order by T1.deptno

DROP table tmb1

Easwarapillai SSC Journeyman Points: 96 More actions · Answer 2

the 3rd maximum value must be 1000 not 2000

your sql returns as follows

deptno sal_3rd

-------------------- --------------------

1 2000

2 1000

3 2000

what i need is

deptno sal_3rd

-------------------- --------------------

1 1000

2 1000

3 2000

because 1000 is the 3rd maximum in deptno 1.

Regards,
Easwar

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Now you know why you should ALWAYS post the expected results .

create table tmb1 (deptno numeric, sal numeric)

insert into tmb1 (deptno, sal ) values (1, 1000)

insert into tmb1 (deptno, sal ) values (1, 2000)

insert into tmb1 (deptno, sal ) values (1, 3000)

insert into tmb1 (deptno, sal ) values (2, 1000)

insert into tmb1 (deptno, sal ) values (2, 2000)

insert into tmb1 (deptno, sal ) values (2, 3000)

insert into tmb1 (deptno, sal ) values (3, 1000)

insert into tmb1 (deptno, sal ) values (3, 2000)

insert into tmb1 (deptno, sal ) values (3, 3000)

insert into tmb1 (deptno, sal ) values (3, 4000)

Select T1.deptno, Min(sal) as sal_3rd

from tmb1 T1

where exists ( Select *

from tmb1 T2

where T1.deptno = T2.deptno and T1.sal = T2.sal

and T2.sal in ( Select distinct top 3 Sal

from tmb1 T3

where T3.deptno = T1.deptno

order by Sal Desc))

group by T1.deptno

order by T1.deptno

DROP table tmb1

Easwarapillai SSC Journeyman Points: 96 More actions · Answer 4

Easwarapillai

SSC Journeyman

Points: 96

July 13, 2005 at 7:53 am

#573888

thanx remi. it works fine

Regards,
Easwar

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Forgot to ask... do you ALWAYS need the third max or this number can be dynamic (major change in the last subquery)?

lloyd-236007 SSC Enthusiast Points: 197 More actions · Answer 6

Hi Remi,

i could not understand the logic of your code, could you please explain it to me? sorry for being such a dud head.

Also how can the query be modified for 4 , or 2nd highest?

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 7

Maybe this one is easier to read (I haven't tested performance):

create table tmb1 (deptno numeric, sal numeric)

insert into tmb1 (deptno, sal ) values (1, 1000)

insert into tmb1 (deptno, sal ) values (1, 2000)

insert into tmb1 (deptno, sal ) values (1, 3000)

insert into tmb1 (deptno, sal ) values (2, 1000)

insert into tmb1 (deptno, sal ) values (2, 2000)

insert into tmb1 (deptno, sal ) values (2, 3000)

insert into tmb1 (deptno, sal ) values (3, 1000)

insert into tmb1 (deptno, sal ) values (3, 2000)

insert into tmb1 (deptno, sal ) values (3, 3000)

insert into tmb1 (deptno, sal ) values (3, 4000)

select distinct t1.deptno, t1.sal from tmb1 t1

where

t1.sal in

(select distinct top 3 t2.sal from tmb1 t2 where t1.deptno = t2.deptno order by t2.sal desc)

and t1.sal not in

(select distinct top 2 t2.sal from tmb1 t2 where t1.deptno = t2.deptno order by t2.sal desc)

DROP table tmb1

To obtain 2nd highest, replace "top 3" by "top 2" and "top 2" by "top 1"...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Ninja's_RGR'us

SSC Guru

Points: 294069

July 14, 2005 at 6:37 am

#574331

What do you do when it's not in top 25?, top 50%?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

That's why I asked you if the top 3 was static, that's another completly different solution if that number is dynamic.

Logic :

Select Cols from table where exists (check if line is in the top 3 distinct items.)

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 10

The original problem didn't say what to do if there is no N'th highest salary. My solution doesn't output a deptno in this case, so I guess that's OK

To obtain a non-static solution, I would exec() my query.

I admit I can't modify my query to solve a top 50 % problem

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

That's why I worked to create mine... but if he needs top n that's gonna be an entirely different solution.