2Nd Highest Salary Inwards Oracle Using Row_Number In Addition To Orbit Inwards Oracle In Addition To Mssql

This is the minute article close calculating 2nd highest salary inwards SQL. In the first part, you lot accept learned how to respect the minute highest salary inwards MySQL, SQL SERVER too past times using ANSI SQL, which should likewise piece of work inwards all database which confirms ANSI touchstone e.g. PostgreSQL, SQLLite etc. In this part, I volition exhibit you lot how to respect the 2nd maximum salary inwards Oracle too SQL SERVER using ROW_NUMBER(), RANK() too DENSE_RANK() method. These are window role inwards Oracle, which tin last used to assign unique row id, or grade to each row based on whatsoever column too thence select the right row. For example, to calculate the 2nd highest salary, nosotros tin practise row numbers using ROW_NUMBER() role over salary too thence acquire the minute row, which would last your 2nd maximum salary. Though these ranking functions handles duplicates differently, thence depending upon whether your tabular array has the duplicate salary, you lot demand to select either ROW_NUMBER(), RANK() or DENSE_RANK(), which handgrip duplicate differently. This is likewise 1 of the most frequently asked SQL Interview questions for your reference.



SQL to cook Schema inwards Oracle database

Here are the SQL queries to practise tables for this problem. It showtime practise an Employee tabular array too thence insert about dummy information alongside 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 inwards Oracle using ROW_NUMBER

Here is the SQL query to respect the minute highest salary inwards 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 work alongside this approach is that if you lot accept duplicate rows (salaries) thence 2nd too third maximum both volition last same.

2nd maximum salary inwards Oracle using RANK

select * from ( select e.*, rank() over (order by salary desc) as grade from Employee e ) where grade = 2;  Output: Mr. B 5000 2

If you lot usage RANK thence same salaries volition accept the same rank, which way 2nd maximum volition ever last same but at that spot won't last whatsoever third maximum. There volition last quaternary maximum.

2nd highest salary inwards 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 only perfect. It volition ever furnish right highest salary fifty-fifty alongside duplicates. For example, if the 2nd highest salary has appeared multiple times they would accept the same rank. So the minute maximum volition ever last same. The adjacent dissimilar salary volition last third maximum equally opposed to quaternary maximum equally was the instance alongside RANK() function. Please see, Microsoft SQL Server 2012 T-SQL Fundamentals to acquire to a greater extent than close the departure betwixt rank() too desnse_rank() role inwards SQL Server.

Nth Highest salary alongside duplicates
In this illustration quaternary highest salary is duplicate, thence if you lot usage row_number() quaternary too fifth highest salary volition last same if you lot usage rank() thence at that spot won't last whatsoever fifth highest salary.

quaternary highest salary using row_number() inwards 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

fifth maximum salary using row_number() inwards 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. Influenza A virus subtype H5N1    3000    5

You tin run across both times it returns alone 3000, fifth maximum should last 1000.

If you lot calculate fifth maximum using RANK() thence you lot won't acquire anything:

select * from ( select e.*, rank() over (order by salary desc) as grade from Employee e ) where grade = 5;  Output:  Record Count: 0;

but DENSE_RANK() volition furnish both quaternary too fifth highest salary correctly equally 3000 too 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

too the fifth 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 close how to calculate minute highest salary inwards Oracle using ROWNUM, RANK() too DENSE_RANK() function.

Here is a prissy summary of departure betwixt RANK, ROW_NUMBER too DENSE_RANK role for your quick reference:

Some to a greater extent than SQL query interview questions too articles:

• What is the departure betwixt truncate too delete inwards SQL (answer)
• What is the departure betwixt UNION too UNION ALL inwards SQL? (answer)
• What is the departure betwixt WHERE too HAVING clause inwards SQL? (answer)
• What is the departure betwixt Correlated too Non-Correlated subquery inwards SQL? (answer)
• 5 Web sites to acquire SQL online for FREE (resource)
• Write SQL queries to respect all duplicate records from the table? (query)
• How to bring together 3 tables inwards 1 SQL query? (solution)

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

Share this post