This is the second article about calculating 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

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

##

Here is the SQL query to find the second highest salary in Oracle using

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

##

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.

##

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

In this example 4th highest salary is duplicate, so if you use

4th highest salary using row_number() in Oracle:

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

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:

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

and the 5th maximum would be:

That's all about

Here is a nice summary of difference between RANK, ROW_NUMBER and DENSE_RANK function for your quick reference:

Some more

Joe Celko's SQL Puzzles and Answers, Second Edition

**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 handles 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.__SQL to build Schema in Oracle database__Here are the SQL queries to create tables for this problem. It first create an Employee table and then insert 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 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 second highest salary in Oracle using ROWNUM, RANK() and DENSE_RANK()**function.Here is a nice summary of difference between RANK, ROW_NUMBER and DENSE_RANK function for your quick reference:

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)

__Further Reading__Joe Celko's SQL Puzzles and Answers, Second Edition

## 2 comments :

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

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

## Post a Comment