Find nth Min/Max values in Sql Server

Finding nth minimum and maximum values in sql server

Finding Nth minimum salary:

1.In SQL Server 2005

SELECT * FROM (SELECT salary,Dense_rank() OVER (ORDER BY Salary) AS Rank FROM employees) t1 WHERE Rank=N

2.In SQL Server 2000/2005

SELECT salary FROM employees e1 WHERE (N = (SELECT COUNT(DISTINCT (e2.salary))
FROM employees e2 WHERE e2.salary <= e1.salary))

Finding Nth maximum salary:

1.In SQL Server 2005

SELECT * FROM (SELECT salary,Dense_rank() OVER (ORDER BY Salary desc) AS Rank FROM employees) t1 WHERE Rank=N

2.In SQL Server 2000/2005

SELECT salary FROM employees e1 WHERE (N = (SELECT COUNT(DISTINCT (e2.salary))
FROM employees e2 WHERE e2.salary >= e1.salary))

Hope you find it useful.