Nth highest salary

The most common and important question asked in interviews that how we can find the Nth highest salary in a table (2^nd highest salary, 3^rd highest salary, or Nth highest salary), where N could be 2^nd, 3^rd, 4^th, 5^th or anything.

Each programmer knows the easiest way to find the n^th highest salary is using SQL (Structured Query Language) in a table.

Whenever the interviewer asks you a question about the 2^nd highest salary, 4^th highest salary, and so on, to solve this question, we should know about some important concepts like a subquery,  function to be used like row_num(), Rank Rank (), etc.

This article will let you know different ways to find the N^th Highest Salary.

Consider the existing tables which have the following records:

Table: Employees

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4

N^th highest salary using a correlated subquery

A correlated subquery is a special type of subquery, where the subquery depends on the main query and is executed for every row returned by the main query.

Syntax:

SELECT salary from employees e1WHERE N-1 = (SELECT COUNT (DISINCT salary) FROM employees e2 WHERE e2.salary > e1.salary)

Where N can be replaced with numbers 2^, 3, or 4.

Example 1: Write a query to find the 4^th highest salary:

SELECT employeeid, salary from employees e1WHERE 3 = (SELECT COUNT (DISTINCT salary) FROM employees e2 WHERE e2.salary > e1.salary)

Where N = 4, N -1: 4 -1 = 3.

4^th highest salary from the table

Output:

Employeeid	Salary
3003	58500

Explanation:

The distinct keyword is there to handle the duplicated salaries in the table. To search for the N^th highest salary, we only consider non-duplicates salaries. The highest salary means no salary is higher than it. The second-highest salary means only one salary is greater than it. The third-highest salary means only two salaries are higher than the third salary, similarly Nth highest salary means N-1 salaries are greater than it.

The fourth highest salary is 58500 of employee id 3003.

Example 2: Write a query to find the 3^rd highest salary:

SELECT employeeid, salary from employees e1WHERE 3 - 1 = (SELECT COUNT (DISTINCT salary) FROM employees e2 WHERE e2.salary > e1.salary)

Where N = 3, N -1: 3 -1 = 2.

2^nd highest salary from the table

Output:

Employeeid	Salary
1002	60000
4003	60000

The second highest salary is 58500 of two employee id 1002 and employee id 4003.

Example 3: Write a query to find the 5^th highest salary:

SELECT employeeid, salary from employees e1WHERE 6 - 1 = (SELECT COUNT (DISTINCT salary) FROM employees e2 WHERE e2.salary > e1.salary)

Where N = 6, N -1: 6 -1 = 5.

5^th highest salary from the table

Output:

Employeeid	Salary
4002	54500

The fifth highest salary is 54500 of employee id 4002.

N^th Highest salary using LIMIT keyword

Syntax:

SELECT salary FROM employees ORDER BY salary desc LIMIT N-1, 1;

Example 1: Write a query to find 2^nd highest salary.

SELECT employeeid, salary FROM employees ORDER BY salary desc LIMIT 1, 1;

Output:

Employeeid	Salary
2002	65500

Limit 1, 1 in the query says how much the highest salary is to be displayed.

If you write 1, 2 outputs will be like this

Employeeid	Salary
2002	65500
4001	60500

N^th Highest salary using TOP keyword

The scenario is to calculate the N^th highest employee salary from the employees' table. The steps are as follows:

1. First step is to search the employees’ TOP unique salary from the table.
2. The next step is to calculate the minimum salary among all the salaries resulting from the first step. By this step, we get N^th highest salary.
3. From the result of the above steps, identify the details of the employee whose salary is the minimum salary.

Example 1:

SELECT * FROM employees WHERE salary = (SELECT MIN(salary) FROM employees WHERE salary IN(SELECT DISTINCT TOP N salary from employees ORDER BY salary DESC ));

The above SQL statement is used to find the details of the employees with the N^th highest salary.

Let’s see the explanation of the above SQL statement in brief:

Consider N = 4.

Whenever any SQL query includes a sub-query, remember the inner query will execute first then the outer query will get executed.

The following result will be generated by the query "SELECT DISTINCT TOP N salary from employees ORDER BY salary DESC," which will generate the following result.

Salary

65500

60500

60000

58500

The next outer query is: "SELECT MIN(salary) FROM employees WHERE salary IN (the output of a previous SQL query.

Salary

58500

From the above result, it is verified that the required fourth-highest salary is 58500.

Lastly, the main query is SELECT * FROM employees WHERE salary = output of previous SQL query. The output of this query will be the result of the employees having the fourth-highest salary.

FIRST_NAME	SALARY
DEEPAM	58500

Example 2:

SELECT * FROM employees WHERE salary = (SELECT MIN(salary) FROM employees WHERE salary IN(SELECT DISTINCT TOP N salary from employees ORDER BY salary DESC ));

The above SQL statement is used to find the details of the employees with the N^th highest salary.

Let’s see the explanation of the above SQL statement in brief:

Consider N = 5.

Whenever any SQL query includes a sub-query, remember the inner query will execute first then the outer query will get executed.

The following result will be generated by the query "SELECT DISTINCT TOP N salary from employees ORDER BY salary DESC," which will generate the following result.

Salary

65500

60500

60000

58500

55500

The next outer query is: "SELECT MIN(salary) FROM employees WHERE salary IN (the output of a previous SQL query.

Salary

55500

From the above result, it is verified that the required fifth-highest salary is 55500.

Lastly, the main query is SELECT * FROM employees WHERE salary = output of previous SQL query. The output of this query will be the result of the employees having the fifth-highest salary.

FIRST_NAME	SALARY
PRACHI	55500

Nth Highest salary using Row_Num() function

Example:

SELECT MIN(salary) FROM(SELECT DISTINCT salary FROM employees ORDER  BY salary DESC) WHERE rownum < 3;

• To calculate the third-highest salary, use rownum < 4
• To calculate the second-highest salary, use rownum < 3

Output:

MIN(salary

60500

Let's see how the query works:

Step 1: The query includes subquery means inner query and outer query. We all know when the subquery is used in the query, the inner query is first executed

First, the inner query will get executed then the outer queries will be executed based on the output produced by the inner query:

Inner query:    

SELECT MIN(salary) FROM(SELECT DISTINCT salary FROM employees ORDER  BY salary DESC) WHERE rownum < 3;

Output of the inner query:

Salary
65500
60500
60000
58500
55500
54500
50500
50000

As we used a distinct keyword in the query, the duplicate salary will get eliminated. Unique salary will be displayed as the result of the inner query.

Step 2: As the server is done with an inner query, we are executing the outer query of the nested query into the output we obtained from the inner query

SELECT MIN(salary) FROM(SELECT DISTINCT salary FROM employees ORDER  BY salary DESC) WHERE rownum < 3;

SELECT MIN(salary) FROM (inner query output): select minimum salary from the inner query output, which is 50000 and 50000 is not the second-highest salary, because of which we have used rownum < 3, which will give the number of rows from the top which is less than 3 means only 2.

The output of WHERE rownum < 3:

Salary

65500

60500

Step 3: The Last part of the query, which is the SELECT MIN(salary) from (the output  of WHERE rownum<3):

The final output of the query:

Salary

60500

60500 is the second-highest salary on the employees' table.

Nth Highest salary using Rank Rank () function

Example:

SELECT * FROM(SELECT First_Name, salary, dense_rank()      OVER(ORDER BY salary DESC) rank FROM employees) WHERE rank =#

- To calculate the third-highest salary, use num =  3

- To calculate the second-highest salary, use num = 2

We will go for num = 2.

The final output:

First_Name	Salary	Rank
Rajesh	60500	2

Output of the inner query:

SELECT(First_Name, Salary, dense_rank() OVER(ORDER BY salary DESC) Rank FROM employees

The Dense_rank() function calculates the Rank of each row in an order group of rows in ascending order and returns the Rank as a number. The Rank starts from 1 and so on.

In case of two or more than two rows have the same salary, it assigns an equal rank to all the rows.

Output of the inner query:

First_name	Salary	Rank
VAIBHAVI	65500	1
BHAVESH	65500	1
RAJESH	60500	2
VAIBHAV	60000	3
RUCHIKA	60000	3
DEEPAM	58500	4
PRACHI	55500	5
PRANOTI	55500	5
ARCHIT	55500	5
ASHWINI	54500	6
NIKHIL	50500	7
ANUJA	50500	7
RUCHIKA	50000	8

In the output, we can see the same ranking to duplicate salary.

Step 2: As the server is done with an inner query, we are executing the outer query of the nested query into the output we obtained from the inner query

SELECT * FROM(SELECT(First_Name, Salary, dense_rank() OVER(ORDER BY salary DESC) rank FROM employees) WHERE rank = #

Select * from will select all the rows which are not the second-highest salary because we have used RankRank where num = 2 will give matching rows according to the value entered by the user for num.

As we used num = 2, the output will be

First_name	Salary	Rank
RAJESH	60500	2

• To find the third salary set num = 3,
• To find the fourth salary set num = 4, and so on.