Monday, May 16, 2011

Second Highest Salary- Sql Query


Create Table #Emp(id int identity primary key,
    Emp varchar(100),
    Salary int)

insert into #Emp
select 'XXXX',17000 union all
select 'DDDD',21000 union all
select 'WWWW',30000 union All
Select 'HHHH',21000 union All
select 'CCCC',30000 union All
Select 'TTTT',21000 union All
SElect 'PPPP',21000

select * from #Emp

select #Emp.* 
from (select ROW_NUMBER() over (order by salary desc) as RowNumber, Salary
   from #Emp
    group by Salary) as tempEmp inner join #Emp on tempEmp.Salary = #Emp.Salary
   where tempEmp.RowNumber=2

Drop Table #Emp

Data Will Look like this

Data

Output will be like this

Output
Post a Comment