2Nd Highest Salary Inwards Oracle Using Row_Number As Well As Order Inwards Oracle As Well As Mssql

This is the instant article close calculating 2nd highest salary inwards SQL. In the first part, you lot convey learned how to notice the instant highest salary inwards MySQL, SQL SERVER together with yesteryear using ANSI SQL, which should every bit good piece of work inwards all database which confirms ANSI touchstone e.g. PostgreSQL, SQLLite etc. In this part, I volition demonstrate you lot how to notice the 2nd maximum salary inwards Oracle together with SQL SERVER using ROW_NUMBER(), RANK() together with DENSE_RANK() method. These are window component inwards Oracle, which tin endure used to assign unique row id, or seat to each row based on whatsoever column together with thus select the right row. For example, to calculate the 2nd highest salary, nosotros tin practise row numbers using ROW_NUMBER() component over salary together with thus acquire the instant row, which would endure your 2nd maximum salary. Though these ranking functions handles duplicates differently, thus 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 every bit good 1 of the most frequently asked SQL Interview questions for your reference.



SQL to gear upward Schema inwards Oracle database

Here are the SQL queries to practise tables for this problem. It commencement practise an Employee tabular array together with thus insert only about 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 notice 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 occupation amongst this approach is that if you lot convey duplicate rows (salaries) thus 2nd together with third maximum both volition endure same.

2nd maximum salary inwards Oracle using RANK

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

If you lot utilisation RANK thus same salaries volition convey the same rank, which way 2nd maximum volition e'er endure same but at that topographic point won't endure whatsoever third maximum. There volition endure 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 e'er provide right highest salary fifty-fifty amongst duplicates. For example, if the 2nd highest salary has appeared multiple times they would convey the same rank. So the instant maximum volition e'er endure same. The side yesteryear side unlike salary volition endure third maximum every bit opposed to quaternary 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 close the divergence betwixt rank() together with desnse_rank() component inwards SQL Server.

Nth Highest salary amongst duplicates
In this illustration quaternary highest salary is duplicate, thus if you lot utilisation row_number() quaternary together with fifth highest salary volition endure same if you lot utilisation rank() thus at that topographic point won't endure 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 meet both times it returns solely 3000, fifth maximum should endure 1000.

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

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

but DENSE_RANK() volition provide both quaternary together with fifth highest salary correctly every bit 3000 together with 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

together with 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 instant highest salary inwards Oracle using ROWNUM, RANK() together with DENSE_RANK() function.

Here is a overnice summary of divergence betwixt RANK, ROW_NUMBER together with DENSE_RANK component for your quick reference:

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

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

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