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:
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 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.
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 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:
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:
- The Complete SQL Bootcamp by Josh Portilla, a Data Scientist, on Udemy or
- SQL for Newbs: Data Analysis for Beginners by David Kim and Peter Sefton's course on Udemy.
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)
5 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.
sir ji kisi ki same salary hogi to ye aap ki query kam nahii karegi...
same salary hogi to query konsi aaegi???
pls answer???????????????????????????????
Same salary hai toh row_number() and dense_rank() work karenge but rank() nhi work karega
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
Post a Comment