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

This is the minute article nearly calculating 2nd highest salary inwards SQL. In the first part, yous convey learned how to respect the minute highest salary inwards MySQL, SQL SERVER together with past times using ANSI SQL, which should too piece of work inwards all database which confirms ANSI criterion e.g. PostgreSQL, SQLLite etc. In this part, I volition exhibit yous how to respect the 2nd maximum salary inwards Oracle together with SQL SERVER using ROW_NUMBER(), RANK() together with DENSE_RANK() method. These are window business office inwards Oracle, which tin give the axe live on used to assign unique row id, or grade to each row based on whatever column together with and then select the right row. For example, to calculate the 2nd highest salary, nosotros tin give the axe practise row numbers using ROW_NUMBER() business office over salary together with and then acquire the minute row, which would live on your 2nd maximum salary. Though these ranking functions handles duplicates differently, hence depending upon whether your tabular array has the duplicate salary, yous demand to select either ROW_NUMBER(), RANK() or DENSE_RANK(), which grip duplicate differently. This is too 1 of the most frequently asked SQL Interview questions for your reference.



SQL to construct Schema inwards Oracle database

Here are the SQL queries to practise tables for this problem. It get-go practise an Employee tabular array together with and then insert to a greater extent than or less 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 yous convey duplicate rows (salaries) together with then 2nd together with tertiary maximum both volition live on 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 yous utilization RANK together with then same salaries volition convey the same rank, which agency 2nd maximum volition ever live on same but at that spot won't live on whatever tertiary maximum. There volition live on 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 simply perfect. It volition ever supply right highest salary fifty-fifty alongside duplicates. For example, if the 2nd highest salary has appeared multiple times they would convey the same rank. So the minute maximum volition ever live on same. The side past times side dissimilar salary volition live on tertiary maximum every bit opposed to fourth maximum every bit was the instance alongside RANK() function. Please see, Microsoft SQL Server 2012 T-SQL Fundamentals to larn to a greater extent than nearly the departure betwixt rank() together with desnse_rank() business office inwards SQL Server.

Nth Highest salary alongside duplicates
In this illustration fourth highest salary is duplicate, hence if yous utilization row_number() fourth together with fifth highest salary volition live on same if yous utilization rank() together with then at that spot won't live on 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. Influenza A virus subtype H5N1    3000    5

You tin give the axe encounter both times it returns exclusively 3000, fifth maximum should live on 1000.

If yous calculate fifth maximum using RANK() together with then yous 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 supply both fourth 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 nearly how to calculate minute highest salary inwards Oracle using ROWNUM, RANK() together with DENSE_RANK() function.

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

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

• What is the departure betwixt truncate together with delete inwards SQL (answer)
• What is the departure betwixt UNION together with UNION ALL inwards SQL? (answer)
• What is the departure betwixt WHERE together with HAVING clause inwards SQL? (answer)
• What is the departure betwixt Correlated together with Non-Correlated subquery inwards SQL? (answer)
• 5 Web sites to larn SQL online for FREE (resource)
• Write SQL queries to respect 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

Sumber https://javarevisited.blogspot.com/

Share this post