second largest - 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 2005 Forums

Transact-SQL (2005)

second largest

Author

Topic

rajeshskpm
Starting Member

10 Posts

Posted - 2009-01-30 : 04:56:42

table : emp(id,name ,salary)

how to find second largest salry from emp table using query??

rajesh

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-30 : 04:58:06

select * from
(select row_number() over( order by id)as rid,* from emp)s where rid =2

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-30 : 04:58:18

select * from
( select *,row_number() over ( partition by salary order by salary desc ) as rn from urtable ) t
where t.rn = 2

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-30 : 06:00:43

try this

SELECT max(salary)
from emp
where salary < ( select max(salary) from emp )

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-30 : 06:03:09

quote:
Originally posted by bklr

select * from
(select row_number() over( order by id)as rid,* from emp)s where rid =2

Yours query is not correct. It will pull only second record which is inserted in emp table but not the record with second highest salary..Please check it once.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-30 : 06:05:45

quote:
Originally posted by raky

quote:
Originally posted by bklr

select * from
(select row_number() over( order by id)as rid,* from emp)s where rid =2

Yours query is not correct. It will pull only second record which is inserted in emp table but not the record with second highest salary..Please check it once.

nice catch i forgot to keep partition by with salary

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 09:47:50

quote:
Originally posted by Nageswar9

select * from
( select *,row_number() over ( partition by salary order by salary desc ) as rn from urtable ) t
where t.rn = 2

this will give second record belonging to each salary group.

it should be


SELECT distinct salary
FROM
(
select *,dense_rank() over (order by salary  desc ) as rnk from urtable
)t
where rnk=2

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-30 : 11:18:17

I bet this is an interview question - I've seen that one many times.

You can also do it in the following ways in case you don't have SQL Server 2005:

SELECT TOP 1 salary FROM
(SELECT TOP 2 salary FROM emp ORDER BY salary desc) t
ORDER BY salary ASC

Another way is using MIN():

SELECT MIN(salary) FROM
(SELECT TOP 2 salary FROM emp ORDER BY salary DESC) t

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources