# Nth highest salary

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

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

Whenever the interviewer asks you a question about the 2nd highest salary, 4th 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 Nth Highest Salary.

Consider the existing tables which have the following records:

## Nth 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 4th 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.

4th highest salary from the table

Output:

Explanation:

The distinct keyword is there to handle the duplicated salaries in the table. To search for the Nth 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 3rd 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.

2nd highest salary from the table

Output:

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

Example 3: Write a query to find the 5th 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.

5th highest salary from the table

Output:

The fifth highest salary is 54500 of employee id 4002.

Nth 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 2nd highest salary.

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

Output:

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

## Nth Highest salary using TOP keyword

The scenario is to calculate the Nth 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 Nth 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 Nth 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.

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

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.

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 Nth 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.

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

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.

## 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:

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:

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:

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:

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 =&num;``

- 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:

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:

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 = &num;``

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

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