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.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook