Friday, April 14, 2023

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.

2nd highest salary in Oracle using ROW_NUMBER and RANK in Oracle and 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:

Second highest salary using RANK, ROW_NUMBER and DENSE_RANK

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)



5 comments:

  1. wrong out put will be print in second Highest salary output is 7000 1st Highest salary is10000 in table

    ReplyDelete
  2. @Unknown, there is no 10K or 10,000 salary in table, its just 1,000, which means 7,000 is highest salary and 5,000 is the second highest salary.

    ReplyDelete
  3. sir ji kisi ki same salary hogi to ye aap ki query kam nahii karegi...
    same salary hogi to query konsi aaegi???

    pls answer???????????????????????????????

    ReplyDelete
    Replies
    1. Same salary hai toh row_number() and dense_rank() work karenge but rank() nhi work karega

      Delete
  4. SELECT * from (select e.*,ROW_NUMBER() over(order by salary desc) as rownum from employees_salary as e)as maxsal where rownum=2; / its correct query

    ReplyDelete