2Nd Highest Salary Inwards Oracle Using Row_Number Too Grade Inwards Oracle Too Mssql

This is the instant article nearly calculating 2nd highest salary inwards SQL. In the first part, you lot accept learned how to uncovering the instant highest salary inwards MySQL, SQL SERVER as well as past times using ANSI SQL, which should likewise piece of work inwards all database which confirms ANSI measure e.g. PostgreSQL, SQLLite etc. In this part, I volition present you lot how to uncovering the 2nd maximum salary inwards Oracle as well as SQL SERVER using ROW_NUMBER(), RANK() as well as DENSE_RANK() method. These are window business office inwards Oracle, which tin endure used to assign unique row id, or rate to each row based on whatever column as well as thence select the right row. For example, to calculate the 2nd highest salary, nosotros tin practice row numbers using ROW_NUMBER() business office over salary as well as thence acquire the instant row, which would endure 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 grip duplicate differently. This is likewise i of the most frequently asked SQL Interview questions for your reference.



SQL to construct Schema inwards Oracle database

Here are the SQL queries to practice tables for this problem. It kickoff practice an Employee tabular array as well as thence insert roughly dummy information amongst 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 uncovering the instant 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 amongst this approach is that if you lot accept duplicate rows (salaries) thence 2nd as well as third maximum both volition endure same.

2nd maximum salary inwards Oracle using RANK

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

If you lot usage RANK thence same salaries volition accept the same rank, which way 2nd maximum volition e'er endure same but at that spot won't endure whatever third maximum. There volition endure fourth 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 e'er provide right highest salary fifty-fifty amongst duplicates. For example, if the 2nd highest salary has appeared multiple times they would accept the same rank. So the instant maximum volition e'er endure same. The adjacent unlike salary volition endure third maximum every bit opposed to fourth maximum every bit was the instance amongst RANK() function. Please see, Microsoft SQL Server 2012 T-SQL Fundamentals to acquire to a greater extent than nearly the departure betwixt rank() as well as desnse_rank() business office inwards SQL Server.

Nth Highest salary amongst duplicates
In this illustration fourth highest salary is duplicate, thence if you lot usage row_number() fourth as well as fifth highest salary volition endure same if you lot usage rank() thence at that spot won't endure whatever fifth highest salary.

fourth 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. H5N1    3000    5

You tin come across both times it returns alone 3000, fifth maximum should endure 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 rate from Employee e ) where rate = 5;  Output:  Record Count: 0;

but DENSE_RANK() volition provide both fourth as well as fifth highest salary correctly every bit 3000 as well as 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

as well as 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 nearly how to calculate instant highest salary inwards Oracle using ROWNUM, RANK() as well as DENSE_RANK() function.

Here is a overnice summary of departure betwixt RANK, ROW_NUMBER as well as DENSE_RANK business office for your quick reference:

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

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

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

Bagikan Artikel ini