,
Share this with your friends 
 

Find nth highest salary employee

8 ratings Views 686 
Author: TEJINDER (TEJINDER SINGH)  View Profile |  View other solutions by this author

Question / Problem


different solution for Finding nth highest salary employee

Solution

--finding nth highest salary of employee
create table EmployeeSalary(EmployeeName varchar(255), Salary numeric(18,2))
go
insert into EmployeeSalary
values('test10',14569)
go
insert into EmployeeSalary
values('test9',14785)
go
insert into EmployeeSalary
values('test8',14750)
go
insert into EmployeeSalary
values('test7',14950)
go
insert into EmployeeSalary
values('test6',16950)
go
insert into EmployeeSalary
values('test5',56950)
go
insert into EmployeeSalary
values('test4',46950)
go
insert into EmployeeSalary
values('test3',18733)
go
insert into EmployeeSalary
values('test2',18900)
go
insert into EmployeeSalary
values('test1',42850)
go
insert into EmployeeSalary
values('test',22850)
go

--fastest 1st runnerup
--logic 1

Declare @n as int
set @n = 5
select EmployeeName, Salary From (select * , (select count(*) from EmployeeSalary s where s.salary >= EmployeeSalary.Salary) as counter from EmployeeSalary
) as other where counter = @n

go
--fastest 2nd runnerup, will not work in version prior than sql server 2005
--logic 2
--use dense_rank at the place of row_number if there is more than one employee have same salary
Declare @n as int
set @n = 5
select EmployeeName, Salary from (select *, row_number() over(order by Salary desc) as seq from EmployeeSalary) as aa
where seq = @n

go
--slowest
--logic 3
-- also not able to pick employeename for that salary, if u want so u have to write one more outer query

Declare @n as int
set @n = 5
select Min(Salary) from EmployeeSalary
where Salary in (select top(@n) salary from EmployeeSalary order by Salary Desc)
go

--fastest
--logic 4

Declare @n as int
set @n = 5
Select EmployeeName, Salary from EmployeeSalary
where @n = (select count(*) from EmployeeSalary e where e.salary >= EmployeeSalary.salary)
go

-- fastest 3rd runnerup
-- logic 5

Declare @n as int
set @n = 5
Select Top 1 EmployeeName, Salary From EmployeeSalary
where Salary in (select top(@n) salary from EmployeeSalary order by Salary Desc)
order by Salary asc
go

Applies to

Microsoft SQL Server 2005 ,Microsoft SQL Server 2000

Rank It

Login to rank it

Report


Advertisement