Finding nth highest values... Script in T-SQL

The following script will gives you the nth highest values.

It will find nth highest salary from employee table

which has empid,salary fields.

Set value of @nthhighest variable to whatever number you want

to assign. Result will be according to the specified value.

declare @nthhighest int

set @nthhighest = 5

select * from employee order by salary desc

select top 1 salary from 

(

select top (@nthhighest) salary from employee

order by salary desc

) as employee

order by salary 

SELECT max(salary) FROM employee WHERE salary NOT IN 

(

SELECT top (@nthhighest - 1)  salary FROM employee order by salary desc

)