SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 second largest		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rajeshskpm
Starting Member

India
10 Posts
Posted - 01/30/2009 :  04:56:42  Show Profile  Reply with Quote
table : emp(id,name ,salary)

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




rajesh

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts
Posted - 01/30/2009 :  04:58:06  Show Profile  Reply with Quote
select * from
(select row_number() over( order by id)as rid,* from emp)s where rid =2
Go to Top of Page

Nageswar9
Aged Yak Warrior

India
600 Posts
Posted - 01/30/2009 :  04:58:18  Show Profile  Reply with Quote
select * from
( select *,row_number() over ( partition by salary order by salary desc ) as rn from urtable ) t
where t.rn = 2
Edited by - Nageswar9 on 01/30/2009 05:03:03
Go to Top of Page

raky
Aged Yak Warrior

767 Posts
Posted - 01/30/2009 :  06:00:43  Show Profile  Reply with Quote
try this


SELECT max(salary)
from emp
where salary < ( select max(salary) from emp )
Go to Top of Page

raky
Aged Yak Warrior

767 Posts
Posted - 01/30/2009 :  06:03:09  Show Profile  Reply with Quote
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.
Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts
Posted - 01/30/2009 :  06:05:45  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48073 Posts
Posted - 01/30/2009 :  09:47:50  Show Profile  Reply with Quote
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
Go to Top of Page

Skorch
Constraint Violating Yak Guru

USA
295 Posts
Posted - 01/30/2009 :  11:18:17  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000