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)
AS
(
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.