Get the nth highest salary using Sql Server 2005

A very common requirement is to get the 1st highest, 2nd highest salary and so on, of an employee in an organization. Let us see how to do it:

Sample Table


DROP TABLE #Employees
GO

CREATE TABLE #Employees (EmpID int, EmpName varchar(10), Salary int)
INSERT #Employees SELECT 1,'Tim',345345
INSERT #Employees SELECT 2,'Jill',76845
INSERT #Employees SELECT 3,'Kathy',234545
INSERT #Employees SELECT 4,'Jack',74564
INSERT #Employees SELECT 5,'Harry',56756456
INSERT #Employees SELECT 6,'Arnol',75675
INSERT #Employees SELECT 7,'Dave',76566
INSERT #Employees SELECT 8,'Zinnade',58776
INSERT #Employees SELECT 9,'Iflar',345567
INSERT #Employees SELECT 10,'Jimmy',76766

Highest Salary Of an Employee

SELECT EmpName, Salary
from
(
SELECT EmpName, Salary, Row_Number() OVER(ORDER BY SALARY DESC) AS 'Salaries'
FROM #Employees
) emp
WHERE Salaries = 1

2nd highest salary


SELECT EmpName, Salary
from
(
SELECT EmpName, Salary, Row_Number() OVER(ORDER BY SALARY DESC) AS 'Salaries'
FROM #Employees
) emp
WHERE Salaries = 2

Similarly you can now find the nth maximum from any table.