SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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:

Table: Employees

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAC#5
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4

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:

EmployeeidSalary
300358500

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:

EmployeeidSalary
100260000
400360000

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:

EmployeeidSalary
400254500

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:

EmployeeidSalary
200265500

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

EmployeeidSalary
200265500
400160500

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.

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_NAMESALARY
DEEPAM58500

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.

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_NAMESALARY
PRACHI55500

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

First_NameSalaryRank
Rajesh605002

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_nameSalaryRank
VAIBHAVI655001
BHAVESH655001
RAJESH605002
VAIBHAV600003
RUCHIKA600003
DEEPAM585004
PRACHI555005
PRANOTI555005
ARCHIT555005
ASHWINI545006
NIKHIL505007
ANUJA505007
RUCHIKA500008

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

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



ADVERTISEMENT
ADVERTISEMENT