Tuesday, 17 January 2012

How i Get 5th Highest Salary in sql server 2005


Create A table From Which 5th Highest Salary to be get
by using the command

create table employee ( code char(10),name varchar(200),sal float)

insert some values as

insert into employee values('1001','surender',20000)
insert into employee values('1002','dev',20000)
insert into employee values('1003','raj',25000)
insert into employee values('1004','narender',2000)
insert into employee values('1005','surender',50000)
insert into employee values('1006','surender',60000)
insert into employee values('1007','surender',70000)
insert into employee values('1008','surender',80000)
insert into employee values('1009','surender',90000)
insert into employee values('1010','surender',10000)
insert into employee values('1011','surender',5000)
insert into employee values('1012','surender',3000)
insert into employee values('1013','surender',7000)
insert into employee values('1014','surender',8000)
insert into employee values('1015','surender',9000)

getting all the salary in descending order

select * from employee order by sal desc

now to find top 5 th highest salary from employee table

select top 1 sal from
(
select distinct top 5 sal from employee order by sal desc
) a order by sal

Also We Can Make It General Query For Getting nth Highest Salary In Sql Server as

select top 1 sal from
{
select distinct top n sal from employee order by sal desc
} a order by sal

No comments:

Post a Comment