Find The Nth Highest Salary In SQL Server

In this article, we will learn 'How to Find The Nth Highest Salary In SQL Server' and there different ways to find the Nth highest salary.

This is a very common question to ask in SQL Server interviews. 

Let's see some different ways to find the Nth highest salary in SQL Server

Let's create a table and insert some dummy data into it.

--Creating Employee Table

CREATE TABLE Employee

(Name nvarchar(10), Salary int)

--Inserting values into the Employee table

INSERT INTO Employee(Name, Salary) values('Tom',20000)

INSERT INTO Employee(Name, Salary) values('Tobby',40000)

INSERT INTO Employee(Name, Salary) values('Rob',30000)

INSERT INTO Employee(Name, Salary) values('Ronnie',70000)

INSERT INTO Employee(Name, Salary) values('Bob',20000)

Let's try these different ways to find Nth salary:

Using TOP Keyword

SELECT TOP 1 Salary

FROM (SELECT DISTINCT TOP N Salary

             FROM Employee ORDER BY Salary DESC) AS Temp

ORDER BY Salary

To find Nth highest salary just replace the N with desired number like 2 or 3.

For example:

3rd highest salary

Using SUB-QUERY

SELECT [Name],[Salary]

FROM Employee e

WHERE N-1 =(SELECT COUNT(DISTINCT Salary)

                    FROM Employee e1

                    WHERE e1.salary > e.salary)

To find Nth highest salary just replace the N with desire number like 2 or 3. The inner query will run for every row processed by the outer query, this will be slow in performance.

(To learn more about COUNT() you must read: Usage of Count in SQL)

For example:

3rd highest salary

USING CTE (Common Table Expression)

 

;WITH RESULT AS

(

SELECT Salary,

DENSE_RANK() over

(ORDER BY Salary DESC) AS [RANK]

FROM Employee

)

SELECT TOP 1 Salary

FROM RESULT

WHERE RESULT.RANK=N

To find Nth highest salary just replace the N with desire number like 2 or 3.

For example:

3rd highest salary

(Also read: Full And Differential Backup Using SSMS)