Reteriving Name for 3rd Hightest Salaried Person

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)

Reteriving Name for 3rd Hightest Salaried Person

Author

Topic

Chetan_JMM
Starting Member

4 Posts

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

Hi Group,

How can i reterieve Name for 3rd Highest Value in the table having column of Name, Age & Salary.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-29 : 03:20:16

[code]
Select min(salary) AS salary from
(
select top 3 salary from yourTable order by salary DESC
)T
[/code]

Madhivanan

Failing to plan is Planning to fail

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-29 : 03:21:15

To get the top 3...

select top 3 *
from table
order by salary desc

And then to get the bottom one from there...

select top 1 * from
(select top 3 *
from table
order by salary desc) top3
order by salary asc

Does this work for you?

Rob Farley
http://robfarley.blogspot.com

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-29 : 03:35:13

Madhivanan,

Your answer is good, but it doesn't give the 'name', as asked for.

Rob Farley
http://robfarley.blogspot.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-06-29 : 03:37:12

I think although both approaches gives correct results....madhivanan's approach results in slightly better execution plan.

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-29 : 04:06:12

quote:
Originally posted by rob_farley

Madhivanan,

Your answer is good, but it doesn't give the 'name', as asked for.

Rob Farley
http://robfarley.blogspot.com

If you want other columns also, then use

Select * from yourTable where salary in
(Select min(salary) AS salary from
(
select top 3 salary from yourTable order by salary DESC
)T

)

Madhivanan

Failing to plan is Planning to fail

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-29 : 04:10:37

True. And that has the added bonus of giving everyone who receives that salary. I guess it depends on whether they want a single name, or a bunch of names.

Rob Farley
http://robfarley.blogspot.com

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-29 : 05:57:55

Throwing in a couple of 'with ties' into your idea Rob, will give a bunch of names. Which has the better execution plan for my example data below depends on whether there's an index or not (A vs B).

--data
declare @t table (name varchar(10), age int, salary money) --A
--declare @t table (name varchar(10), age int, salary money, unique clustered (salary, name)) --B
insert @t
          select 'a', 21, 1000
union all select 'b', 34, 2000
union all select 'c', 38, 500
union all select 'd', 18, 5000
union all select 'e', 57, 1000

--calculation (1) (Rob)
select top 1 * from
    (select top 3 * from @t order by salary desc) top3
order by salary asc

--calculation (2) (Madhivanan)
select * from @t where salary in 
    (select min(salary) as salary from
        (select top 3 salary from @t order by salary desc)t )

--calculation (3) (Rob - with ties)
select top 1 with ties * from
    (select top 3 with ties * from @t order by salary desc) top3
order by salary asc

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources