Find Nth Highest Salary in SQL

Board Infinity -

Introduction

A typical SQL Server interview question is to locate the third wage in a database table of employees. The nth highest pay can be discovered in a variety of methods. All of the following questions will also have answers by the time this article is finished.

  1. How to use a subquery in SQL Server to discover the nth highest income
  2. Using a CTE, how to determine the nth highest salary in SQL Server
  3. Finding the second, third, or fifteenth highest wage

For this demonstration, let's utilize the following Employees table.

ID

FirstName

LastName

Gender

Salary

1

Ben

Hoskins

Male

70000

2

Mark

Hastings

Male

60000

3

Steve

Pound

Male

45000

4

Ben

Hoskins

Male

70000

5

Philip

Hastings

Male

45000

6

Mary

Lambeth

Female

30000

7

Valarie

Vikings

Female

35000

8

John

Stanmore

Male

80000

Create table Employees
(
 ID int primary key identity,
 FirstName nvarchar(50),
 LastName nvarchar(50),
 Gender nvarchar(50),
 Salary int
)
Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000)
Insert into Employees values ('Mark', 'Hastings', 'Male', 60000)
Insert into Employees values ('Steve', 'Pound', 'Male', 45000)
Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000)
Insert into Employees values ('Philip', 'Hastings', 'Male', 45000)
Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000)
Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000)
Insert into Employees values ('John', 'Stanmore', 'Male', 80000)

It is simple to locate the greatest wage. Simply use the Max() method, as seen below. Select Max(Salary) from Employees Utilize a sub query combined with the Max() method as demonstrated below to obtain the second-highest income. Select Max(Salary) from Employees where Salary < (Select Max(Salary) from Employees)

Using a subquery, locate the nth highest income.

SELECT TOP 1 SALARY
FROM (
      SELECT DISTINCT TOP N SALARY
      FROM EMPLOYEES
      ORDER BY SALARY DESC
      ) RESULT
ORDER BY SALARY

Utilizing CTE, determine the nth highest salary.

WITH RESULT AS
(
    SELECT SALARY,
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
    FROM EMPLOYEES
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = N

Any of the searches listed above can be used to determine the second highest income. Simply substitute 2 for N. Similarly, simply substitute 3 for N to discover the third highest income.

Please Note: You may have noticed that the following query may be used to retrieve the nth highest income on several websites. Only if there are no duplicates will the following query be successful.

WITH RESULT AS
(
    SELECT SALARY,
           ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER
    FROM EMPLOYEES
)
SELECT SALARY
FROM RESULT
WHERE ROWNUMBER = 3

write your code here: Coding Playground