2Nd Highest Salary Inwards Oracle Using Row_Number Together With Rate Inwards Oracle Together With Mssql

Baca Juga

This is the instant article close calculating 2nd highest salary inward SQL. In the first part, y'all accept learned how to discovery the instant highest salary inward MySQL, SQL SERVER as well as past times using ANSI SQL, which should every bit good piece of work inward all database which confirms ANSI criterion e.g. PostgreSQL, SQLLite etc. In this part, I volition demo y'all how to discovery the 2nd maximum salary inward Oracle as well as SQL SERVER using ROW_NUMBER(), RANK() as well as DENSE_RANK() method. These are window component inward Oracle, which tin hold upwards used to assign unique row id, or seat to each row based on whatever column as well as and therefore select the right row. For example, to calculate the 2nd highest salary, nosotros tin practise row numbers using ROW_NUMBER() component over salary as well as and therefore acquire the instant row, which would hold upwards your 2nd maximum salary. Though these ranking functions handles duplicates differently, therefore depending upon whether your tabular array has the duplicate salary, y'all demand to lead either ROW_NUMBER(), RANK() or DENSE_RANK(), which handgrip duplicate differently. This is every bit good i of the most frequently asked SQL Interview questions for your reference.



SQL to construct Schema inward Oracle database

Here are the SQL queries to practise tables for this problem. It source practise an Employee tabular array as well as and therefore insert or therefore 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 inward Oracle using ROW_NUMBER

Here is the SQL query to discovery the instant highest salary inward 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 y'all accept duplicate rows (salaries) as well as therefore 2nd as well as tertiary maximum both volition hold upwards same.

2nd maximum salary inward 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 y'all utilisation RANK as well as therefore same salaries volition accept the same rank, which agency 2nd maximum volition ever hold upwards same but at that topographic point won't hold upwards whatever tertiary maximum. There volition hold upwards fourth maximum.

2nd highest salary inward 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 but perfect. It volition ever supply 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 ever hold upwards same. The adjacent dissimilar salary volition hold upwards tertiary 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 larn to a greater extent than close the deviation betwixt rank() as well as desnse_rank() component inward SQL Server.

Nth Highest salary amongst duplicates
In this illustration fourth highest salary is duplicate, therefore if y'all utilisation row_number() fourth as well as fifth highest salary volition hold upwards same if y'all utilisation rank() as well as therefore at that topographic point won't hold upwards whatever fifth highest salary.

fourth highest salary using row_number() inward 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() inward 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 exclusively 3000, fifth maximum should hold upwards 1000.

If y'all calculate fifth maximum using RANK() as well as therefore y'all 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 supply 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 close how to calculate instant highest salary inward Oracle using ROWNUM, RANK() as well as DENSE_RANK() function.

Here is a prissy summary of deviation betwixt RANK, ROW_NUMBER as well as DENSE_RANK component for your quick reference:

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

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

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