Mastering SQL: From Fundamentals to Advanced Techniques

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.

How to use a subquery in SQL Server to discover the nth highest income
Using a CTE, how to determine the nth highest salary in SQL Server
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

Find Nth Highest Salary in SQL

Board Infinity

Introduction To SQL: A Complete Guide

SQL Commands-An Introduction

Char and Varchar in SQL

ORDER BY in SQL

5 Resume Writing Myths That You Need to Stop Following Now!

GROUP BY in SQL

Learn How to Perform Union in SQL?

Joins and Its Types in SQL

Rename column name in SQL

Learn How To Compare Two Or More Datetime Entities In SQL With Examples

Difference Between SQL and NoSQL

Constraints in SQL

Learn about Aggregate Functions in SQL

SQL Subqueries

Nested Queries in SQL - Query In Query

SQL WITH Clause – Tutorial and Examples

How to Drop a Table in PostgreSQL?

How to Perform Pivot in SQL?

SQL Window Function

Find Nth Highest Salary in SQL

Working of Correlated SubQueries in SQL

Learn about Stored Procedures in SQL

Triggers in SQL: An Overview and Example

PL/SQL Explained

Introduction