Find nth Highest Value in SQL Server

Problem statement

Suppose I want to find nth highest salary from a table. The following is an example table and data.

CREATE TABLE #SalaryDetail
(
EmpId INT IDENTITY(1,1) NOT NULL,
Empcode VARCHAR(10),
Name VARCHAR(50),
Salary MONEY
)

INSERT INTO #SalaryDetail VALUES('1234','Tejas',54500),
('1235','Jignesh',44500),
('1236','Rakesh',24500),
('1237','Vimal',64500),
('1238','Ashish',4500),
('1239','Keyur',24500),
('1240','Poojan',3800),
('1241','Mihir',64500)

Solution

There are many ways to determine the nth highest value from the table. In this article I will show them one by one.

1. Using Sub query

a) Using Top keyword

DECLARE @nthHighest INT = 2
SELECT TOP(1) * FROM (
SELECT DISTINCT TOP(@nthHighest) Salary
FROM #SalaryDetail
ORDER BY salary DESC) A
ORDER BY salary

b) Using MAX keyword

DECLARE @nthHighest INT = 2
SELECT MAX(Salary) as 'Salary' from #SalaryDetail
where Salary NOT IN
(
SELECT TOP(@nthHighest-1) (SALARY) from #SalaryDetail ORDER BY Salary Desc
)

C) Without TOP Or MAX keyword

DECLARE @nthHighest INT = 2
SELECT * FROM #SalaryDetail s1
WHERE (@nthHighest -1) =
(
SELECT COUNT(DISTINCT Salary) FROM #SalaryDetail s2 WHERE s2.Salary > s1.Salary
)

2. Using DENSE_RANK function

The DENSE_RANK function returns with in the partition of a result set without any gaps. If two or more rows having the same data is within a partition then they receive the same rank. Using this function we can find the nth highest value using the following query.

DECLARE @nthHighest INT = 2
SELECT * FROM (
SELECT EmpId,Empcode,Name,Salary,
DENSE_RANK() OVER(ORDER BY Salary DESC) AS EmpRank
FROM #SalaryDetail
) A WHERE EmpRank = @nthHighest

3. Using DENSE_RANK function and CTE

In the preceding query, we can also write using CTE instead of re-querying on the result set.

DECLARE @nthHighest INT = 2

;WITH CTE(EmpId,Empcode,Name,Salary,EmpRank)

(

SELECT EmpId,Empcode,Name,Salary,

DENSE_RANK() OVER(ORDER BY Salary DESC) AS EmpRank

FROM #SalaryDetail

)

SELECT * FROM CTE WHERE EmpRank = @nthHighest

Summary

Using the preceding methods, we can determine the nth highest value from the database table.