2nd highest salary in Oracle using ROW_NUMBER and RANK in Oracle and MSSQL

This is the second article about calculating the 2nd highest salary in SQL. In the first part, you have learned how to find the second highest salary in MySQL, SQL SERVER and by using ANSI SQL, which should also work in all database which confirms ANSI standard e.g. PostgreSQL, SQLLite, etc. In this part, I will show you how to find the 2nd maximum salary in Oracle and SQL SERVER using ROW_NUMBER(), RANK(), and DENSE_RANK() method. These are window function in Oracle, which can be used to assign unique row id, or rank to each row based on any column and then select the right row.

For example, to calculate the 2nd highest salary, we can create row numbers using ROW_NUMBER() function over salary and then get the second row, which would be your 2nd maximum salary.

Though these ranking functions handle duplicates differently, so depending upon whether your table has the duplicate salary, you need to choose either ROW_NUMBER(), RANK() or DENSE_RANK(), which handle duplicate differently. This is also one of the most frequently asked SQL Interview questions for your reference.


Btw, I expect that you are familiar with SQL and know different clauses and their meaning in a SQL query. If you are not, it's better you gain some experience with SQL by joining a good course like:

1.  The Complete SQL Bootcamp by Josh Portilla, a Data Scientist,  on Udemy or 
2.  SQL for Newbs: Data Analysis for Beginners by David Kim and Peter Sefton's course on Udemy. 

These are the two courses I usually recommend SQL beginners.

SQL to build Schema in Oracle database

Here are the SQL queries to create tables for this problem. It first creates an Employee table and then inserts some dummy data with duplicate salaries.

CREATE TABLE Employee (name varchar(10), salary int);

INSERT INTO Employee VALUES ('Mr. X', 3000);
INSERT INTO Employee VALUES ('Mr. Y', 4000);
INSERT INTO Employee VALUES ('Mr. A', 3000);
INSERT INTO Employee VALUES ('Mr. B', 5000);
INSERT INTO Employee VALUES ('Mr. C', 7000);
INSERT INTO Employee VALUES ('Mr. D', 1000);

2nd highest salary in Oracle using ROW_NUMBER

Here is the SQL query to find the second highest salary in Oracle using row_number() function:

select * from (
select e.*, row_number() over (order by salary desc) as row_num 
from Employee e
) where row_num = 2;

Output:
NAME    SALARY  ROW_NUM
Mr. B    5000     2

The problem with this approach is that if you have duplicate rows (salaries) then 2nd and 3rd maximum both will be same.

2nd maximum salary in Oracle using RANK

select * from (
select e.*, rank() over (order by salary desc) as rank from Employee e
) where rank = 2;

Output:
Mr. B 5000 2

If you use RANK then same salaries will have the same rank, which means 2nd maximum will always be same but there won't be any 3rd maximum. There will be 4th maximum.

2nd highest salary in Oracle using DENSE_RANK

select * from (
select e.*, dense_rank() over (order by salary desc) as dense_rank 
from Employee e
) where dense_rank = 2;

Output
NAME   SALARY  ROW_NUM
Mr. B   5000     2

DENSE_RANK is just perfect. It will always return correct highest salary even with duplicates. For example, if the 2nd highest salary has appeared multiple times they would have the same rank. So the second maximum will always be same. The next different salary will be 3rd maximum as opposed to 4th maximum as was the case with RANK() function. Please see, Microsoft SQL Server 2012 T-SQL Fundamentals to learn more about the difference between rank() and desnse_rank() function in SQL Server.

Nth Highest salary with duplicates

In this example 4th highest salary is duplicate, so if you use row_number() 4th and 5th highest salary will be the same if you use rank() then there won't be any 5th highest salary.

4th highest salary using row_number() in Oracle:

select * from (
select e.*, row_number() over (order by salary desc) as row_num from Employee e
) where row_num = 4;

NAME    SALARY  ROW_NUM
Mr. X    3000     4

5th maximum salary using row_number() in Oracle 11g R2 database:

select * from (
select e.*, row_number() over (order by salary desc) as row_num 
from Employee e
) where row_num = 5;

NAME    SALARY  ROW_NUM
Mr. A    3000    5

You can see both times it returns only 3000, 5th maximum should be 1000.

If you calculate 5th maximum using RANK() then you won't get anything:

select * from (
select e.*, rank() over (order by salary desc) as rank from Employee e
) where rank = 5;

Output: 
Record Count: 0;

but DENSE_RANK() will return both 4th and 5th highest salary correctly as 3000 and 1000.

select distinct salary from (
select e.*, dense_rank() over (order by salary desc) as dense_rank 
from Employee e
) where dense_rank = 4;

Output
SALARY
3000

and the 5th maximum would be:

select distinct salary from (
select e.*, dense_rank() over (order by salary desc) as dense_rank from Employee e
) where dense_rank = 5;

Output:
SALARY
1000

That's all about how to calculate the second highest salary in Oracle using ROWNUM, RANK() and DENSE_RANK() function. Here is a nice summary of the difference between RANK, ROW_NUMBER and DENSE_RANK function for your quick reference:

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Some more SQL query interview questions and articles:

• What is the difference between truncate and delete in SQL (answer)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• What is the difference between WHERE and HAVING clause in SQL? (answer)
• What is the difference between Correlated and Non-Correlated subquery in SQL? (answer)
• 5 Web sites to learn SQL online for FREE (resource)
• Write SQL queries to find all duplicate records from the table? (query)
• How to join three tables in one SQL query? (solution)