How to find nth maximum salary in sql server (using function)

select nth minimum salary,nth maximum salary  from table ins sql server 2008  with using sql function

use test

/create table EmpSalary……………………………..

create  table EmpSalary (SrNo int identity(1,1),EmpName nvarchar(50),Salary int)

insert into EmpSalary values('Dinesh Singh',8000)

insert into EmpSalary values('Roshan Sharma',14000)

insert into EmpSalary values('Rohi Tiwari',15000)

insert into EmpSalary values('Ramesh Katiyar',16000)

insert into EmpSalary values('Vinay Singh',20000)

insert into EmpSalary values('Anil Singh',60000)

/select salary in ascending order……………………………..

Select * from EmpSalary order by Salary asc

/select nth minimum salary from table EmpSalary …… nth= 6

select EmpName as EmployeeName,Salary as EmployeeSalary from EmpSalary es where 6=(Select COUNT(*) from EmpSalary es1 where es.Salary>=es1.Salary)

//select nth minimum salary from table EmpSalary  using function …..return only scalar value

create function getnthmaxsalary (@ntno int) returns int

as

begin

declare @salary int

select  @Salary=es.salary  from EmpSalary es where @ntno=(Select COUNT(*) from EmpSalary es1 where es.Salary<=es1.Salary)

return @salary

end

/execute  this  function……………………………..

select dbo.getnthmaxsalary(5)

//select nth minimum salary from table EmpSalary  using function …..return tabuler value

create function getnthmaxsalary_withtable (@ntno int)  

returns  table

as

return (select EmpName as EmployeeName,Salary as EmployeeSalary from EmpSalary es where @ntno =(Select COUNT(*) from EmpSalary es1 where es.Salary<=es1.Salary))

/execute  this  function……………………………..

select * from dbo.getnthmaxsalary_withtable(2)