5 th highest salary - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

5 th highest salary

Author

Topic

mahimam_2004
Starting Member

40 Posts

Posted - 2006-06-03 : 12:38:39

Hi,
How to get 5th highest salary from the table.
Thanks in advance

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-03 : 12:58:21

Interview question?

CODO ERGO SUM

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-05 : 01:02:45

Hi try This one
Create Table #salary
(
EmpNo Varchar(10),
EmpName Varchar(20),
Salary numeric(13,2)
)

Insert into #salary Select 'EMP001','Name1',10000
Insert into #salary Select 'EMP002','Name2',10001
Insert into #salary Select 'EMP003','Name3',10002
Insert into #salary Select 'EMP004','Name4',10003
Insert into #salary Select 'EMP005','Name5',10004
Insert into #salary Select 'EMP006','Name6',10004
Insert into #salary Select 'EMP007','Name7',10005
Insert into #salary Select 'EMP008','Name8',10005
Insert into #salary Select 'EMP009','Name9',10005
Insert into #salary Select 'EMP010','Name10',10005

SELECT a.empno,a.salary FROM #salary a WHERE 5= (SELECT COUNT(*)+1 FROM #salary b WHERE a.salary<b.salary)
-- KK

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-05 : 02:58:56

Do a google search on "Find Nth maximum"

Madhivanan

Failing to plan is Planning to fail

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-05 : 04:01:05

How about "select min(salary) from (select top 5 salary from #salary order by salary desc) s" ?

Rob Farley
http://robfarley.blogspot.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-05 : 04:14:04

Add a group by to remove any duplicates else you might not get the 'Nth maximum'

select min(salary) 
from 
(
     select top 5 salary 
     from   #salary 
     group by salary
     order by salary desc
) s

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-05 : 04:19:45

Also, as an other method, instead of group by use Distinct top 5 salary to remove duplicates

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-05 : 04:29:46

That depends on how you want to rank.

select min(salary) from (select top 5 salary from #salary group by salary order by salary desc) s

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-05 : 04:31:37

Grrr (note to self: never go for coffee before you reply.)

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-05 : 05:34:05

quote:
Originally posted by PSamsig

Grrr (note to self: never go for coffee before you reply.)

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

That must be quite a long coffee break

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-05 : 10:29:11

quote:
Originally posted by khtan
[brThat must be quite a long coffee break

Its a holliday here, so the coffee breaks are VERY long

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-06 : 02:16:55

quote:
Originally posted by PSamsig

That depends on how you want to rank.
select min(salary) from (select top 5 salary from #salary group by salary order by salary desc) s

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

I Think That will return the second maximum values right.
He asked about 5 th max

Thanks
KK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 02:51:23

as PSamsig said. It actually depends how mahimam wants it.

if the data is 100, 200, 200, 300, 300, 300, 400, 500, 600

select min(salary) from (select top 5 salary from #salary group by salary order by salary desc) s
The above you give you 200 as 5th highest

select min(salary) from (select top 5 salary from #salary ~~group by~~ salary order by salary desc) s
The above you give you 300 as 5th highest

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-06 : 03:10:28

quote:
Originally posted by CSK

quote:
Originally posted by PSamsig

That depends on how you want to rank.
select min(salary) from (select top 5 salary from #salary group by salary order by salary desc) s

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

I Think That will return the second maximum values right.
He asked about 5 th max

Thanks
KK

No. Can you justify your answer?

Madhivanan

Failing to plan is Planning to fail

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-06 : 03:50:12

Hmm... I missed out on subscribing to this thread - I guess because I used the 'quick reply' box.

Yeah, I'm assuming that if you have two second places, then you don't have a 3rd. After all, isn't that the proper way of doing it?

;) I know - it depends on whether you use denserank or rank.

Has anyone mentioned using rowcount? Although in this case, I think 'top 5' is more efficient, particularly if you have an index on the salary column.

Rob

Rob Farley
http://robfarley.blogspot.com

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-06 : 08:12:21

Yes I Justified My Answer

just go through the example

Salary Position
100 12
200 11
300 9
300 9
400 8
500 7
600 2
600 2
600 2
600 2
600 2
700 1

In this above example How we can Say the 300 is the fifth Maximum...?
Can U Please Consider as A rank calculation.........!

Thanks
KK

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-06 : 08:18:56

The asker of the original question cant have meant it ranked like that, in your case there no 5th ranking salery.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-06-06 : 08:59:48

create table Salary ( ID int identity(1,1),EmployeeName varchar(50),Salary int)
insert the values to the salary
create table #temp (ID int identity(1,1),EmployeeName varchar(50),Salary int)

insert #temp select EmployeeName,Salary from Salary order by Salary desc
select * from #temp where ID = 5

MS BLESS US

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-06 : 21:58:28

CSK - your original answer doesn't work, because there might be no record which has exactly 5 larger than it.

Rob Farley
http://robfarley.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources