Find The Nth Highest Salary In SQL Server 2017

Introduction

 
In this article, I am going to explain to you how to find nth highest salary in various ways. This is one of the most common questions asked in an interview in SQL.
 
Below are several ways of finding the Nth Highest Salary.
  1. How to find nth highest salary in SQL Server using a Sub-Query
  2. How to find nth highest salary in SQL Server using a CTE
  3. How to find the 2nd, 3rd or 5th highest salary
Here we will be using SQL Server 2017, or else you can use SQL Server 2008 or above.
 
Read my previous Joins in SQL Server 2017 as a part of this article using the below links before reading this article:
Prerequisites
 
SQL Server 2017 or you can use SQL server 2008 or above version.
 
First, we will create a Database and a table.
 

Creating Database and One Table

 
Step 1 - Create a Database
 
Open your SQL Server and use the following script to create the “chittadb” Database.
  1. Create database chittadb  
Now, select the script query then press F5 or click on the Execute button to execute the above script.
 
You should see a message, “Command(s) completed successfully.” This means your new database has been created.
 
Step 2 - Create a table
 
Open your SQL Server and use the following script to create s table “tbl_Employees”.
  1. Create table tbl_Employees  
  2. (  
  3.      Id int primary key not null identity(1,1),  
  4.      FirstName varchar(50),  
  5.      LastName varchar(20),  
  6.      Location varchar(20),  
  7.      Gender varchar(50),  
  8.      Salary int  
  9. )  
Execute the above query to create “tbl_Employees “.
 
You should see a message, “Command(s) completed successfully.”
 
Now, data is inserted into the table.
  1. Insert into tbl_Employees values ('Chittaranjan''Swain','Odisha''Male', 80000)  
  2. Insert into tbl_Employees values ('Chandin''Swain''Pune','Female', 76000)  
  3. Insert into tbl_Employees values ('Mitu''Pradhan','Delhi''Male', 55000)  
  4. Insert into tbl_Employees values ('Jeni''Swain','Chennai''Female', 76000)  
  5. Insert into tbl_Employees values ('Adyashree''Swain','UK''Female', 49000)  
  6. Insert into tbl_Employees values ('Ram''Kumar','US''Male', 39000)  
  7. Insert into tbl_Employees values ('Jitendra''Gouad','Hydrabad''Male', 35000)  
  8. Insert into tbl_Employees values ('Dibas''Hembram','Bangalore''Male', 55000)  
Execute the above query, you should see a message, “Command(s) completed successfully.”
 
Now retrieve all data from “tbl_Employees” table.
  1. select * from tbl_Employees  
output
 
Find Nth Highest Salary In SQL Server 2017
 
To Find the Highest Salary
 
We can simply use the Max() function as shown below.
  1. Select Max(Salary) as Salary from tbl_Employees  
OutPut
 
Find Nth Highest Salary In SQL Server 2017
 
To Find the Lowest Salary
 
We can simply use the MIN() function as shown below.
  1. Select MIN(Salary) as Salary from tbl_Employees  
OutPut
 
Find Nth Highest Salary In SQL Server 2017
 
To Find Nth Highest Salary Using A Sub-Query
  1. SELECT TOP 1 SALARY  
  2. FROM (  
  3.       SELECT DISTINCT TOP 1 SALARY  
  4.       FROM tbl_Employees  
  5.       ORDER BY SALARY DESC  
  6.       ) RESULT  
  7. ORDER BY SALARY  
Output
 
Find Nth Highest Salary In SQL Server 2017

To Find The Nth Highest Salary Using CTE
  1. WITH RESULT AS  
  2. (  
  3.     SELECT SALARY,  
  4.            DENSE_RANK() OVER (ORDER BY SALARY DESCAS DENSERANK  
  5.     FROM tbl_Employees  
  6. )  
  7. SELECT TOP 1 SALARY  
  8. FROM RESULT  
  9. WHERE DENSERANK = 1  
Output
 
Find Nth Highest Salary In SQL Server 2017
 
To Find The Second Highest Salary
  1. select distinct top 2 salary from tbl_Employees order by Salary desc   
Output
 
Find Nth Highest Salary In SQL Server 2017
 
To find the second highest salary Using a Sub-Query,
 
To get the second highest salary, use a Subquery along with the Max() function
  1. Select Max(Salary) as Salary from tbl_Employees where Salary <(select MAX(Salary) from tbl_Employees)  
Output
 
Find Nth Highest Salary In SQL Server 2017
 
To Find the Second Highest Salary Using a Sub-Query
  1. SELECT TOP 1 SALARY  
  2. FROM (  
  3.       SELECT DISTINCT TOP 2 SALARY  
  4.       FROM tbl_Employees  
  5.       ORDER BY SALARY DESC  
  6.       ) RESULT  
  7. ORDER BY SALARY  
Output
 
Find Nth Highest Salary In SQL Server 2017

To Find Second Highest Salary Using CTE
  1. WITH RESULT AS  
  2. (  
  3.     SELECT SALARY,  
  4.            DENSE_RANK() OVER (ORDER BY SALARY DESCAS DENSERANK  
  5.     FROM tbl_Employees  
  6. )  
  7. SELECT TOP 1 SALARY  
  8. FROM RESULT  
  9. WHERE DENSERANK = 2  
Output
 
Find Nth Highest Salary In SQL Server 2017
 
To Find the Third Highest Salary
  1. select distinct top 3 salary from tbl_Employees order by Salary desc  
Output
 
Find Nth Highest Salary In SQL Server 2017
 
To Find the Third Highest Salary Using a Sub-Query,
  1. SELECT TOP 1 SALARY  
  2. FROM (  
  3.       SELECT DISTINCT TOP 3 SALARY  
  4.       FROM tbl_Employees  
  5.       ORDER BY SALARY DESC  
  6.       ) RESULT  
  7. ORDER BY SALARY  
Output
 
Find Nth Highest Salary In SQL Server 2017

To Find the Third Highest Salary Using CTE
  1. WITH RESULT AS  
  2. (  
  3.     SELECT SALARY,  
  4.            DENSE_RANK() OVER (ORDER BY SALARY DESCAS DENSERANK  
  5.     FROM tbl_Employees  
  6. )  
  7. SELECT TOP 1 SALARY  
  8. FROM RESULT  
  9. WHERE DENSERANK = 3  
Output
 
Find Nth Highest Salary In SQL Server 2017
 
Similarly, to find the 5th highest salary simply replace N with 5. 
 

Conclusion

 
In this article, I explained how to find nth highest salary with several ways in SQL Server with examples. I hope this article has helped you to understand this topic. Post your valuable feedback in the comments section.