2nd highest wage in Oracle utilizing ROW_NUMBER and RANK in Oracle and MSSQL

That is the second article about calculating the 2nd highest wage in SQL. Within the first half, you could have discovered discover the second highest wage in MySQL, SQL SERVER and by utilizing ANSI SQL, which also needs to work in all database which confirms ANSI normal e.g. PostgreSQL, SQLLite, and so on. On this half, I’ll present you discover the 2nd most wage in Oracle and SQL SERVER utilizing ROW_NUMBER(), RANK(), and DENSE_RANK() technique. These are window operate in Oracle, which can be utilized to assign distinctive row id, or rank to every row based mostly on any column after which choose the precise row.

For instance, to calculate the 2nd highest wage, we will create row numbers utilizing ROW_NUMBER() operate over wage after which get the second row, which might be your 2nd most wage.

Although these rating features deal with duplicates in a different way, so relying upon whether or not your desk has the duplicate wage, you have to select both ROW_NUMBER(), RANK() or DENSE_RANK(), which deal with duplicate in a different way. That is additionally one of the crucial often requested SQL Interview questions on your reference.

Btw, I count on that you’re conversant in SQL and know completely different clauses and their that means in a SQL question. In case you are not, it is higher you acquire some expertise with SQL by becoming a member of a superb course like:

1.  The Full SQL Bootcamp by Josh Portilla, a Information Scientist,  on Udemy or 
2.  SQL for Newbs: Information Evaluation for Inexperienced persons by David Kim and Peter Sefton’s course on Udemy. 

These are the 2 programs I normally advocate SQL inexperienced persons.

SQL to construct Schema in Oracle database

Listed here are the SQL queries to create tables for this drawback. It first creates an Worker desk after which inserts some dummy knowledge with duplicate salaries.

CREATE TABLE Worker (title varchar(10), wage int);

INSERT INTO Worker VALUES ('Mr. X', 3000);
INSERT INTO Worker VALUES ('Mr. Y', 4000);
INSERT INTO Worker VALUES ('Mr. A', 3000);
INSERT INTO Worker VALUES ('Mr. B', 5000);
INSERT INTO Worker VALUES ('Mr. C', 7000);
INSERT INTO Worker VALUES ('Mr. D', 1000);

2nd highest wage in Oracle utilizing ROW_NUMBER

Right here is the SQL question to seek out the second highest wage in Oracle utilizing row_number() operate:

choose * from (
choose e.*, row_number() over (order by wage desc) as row_num 
from Worker e
) the place row_num = 2;

Output:
NAME    SALARY  ROW_NUM
Mr. B    5000     2

The issue with this method is that you probably have duplicate rows (salaries) then 2nd and third most each might be identical.

2nd most wage in Oracle utilizing RANK

choose * from (
choose e.*, rank() over (order by wage desc) as rank from Worker e
) the place rank = 2;

Output:
Mr. B 5000 2

For those who use RANK then identical salaries may have the identical rank, which suggests 2nd most will all the time be identical however there will not be any third most. There might be 4th most.

2nd highest wage in Oracle utilizing DENSE_RANK

choose * from (
choose e.*, dense_rank() over (order by wage desc) as dense_rank 
from Worker e
) the place dense_rank = 2;

Output
NAME   SALARY  ROW_NUM
Mr. B   5000     2

DENSE_RANK is simply good. It should all the time return appropriate highest wage even with duplicates. For instance, if the 2nd highest wage has appeared a number of instances they might have the identical rank. So the second most will all the time be identical. The subsequent completely different wage might be third most versus 4th most as was the case with RANK() operate. Please see, Microsoft SQL Server 2012 T-SQL Fundamentals to be taught extra in regards to the distinction between rank() and desnse_rank() operate in SQL Server.

Nth Highest wage with duplicates

On this instance 4th highest wage is duplicate, so in the event you use row_number() 4th and fifth highest wage would be the identical in the event you use rank() then there will not be any fifth highest wage.

4th highest wage utilizing row_number() in Oracle:

choose * from (
choose e.*, row_number() over (order by wage desc) as row_num from Worker e
) the place row_num = 4;

NAME    SALARY  ROW_NUM
Mr. X    3000     4

fifth most wage utilizing row_number() in Oracle 11g R2 database:

choose * from (
choose e.*, row_number() over (order by wage desc) as row_num 
from Worker e
) the place row_num = 5;

NAME    SALARY  ROW_NUM
Mr. A    3000    5

You’ll be able to see each instances it returns solely 3000, fifth most ought to be 1000.

For those who calculate fifth most utilizing RANK() you then will not get something:

choose * from (
choose e.*, rank() over (order by wage desc) as rank from Worker e
) the place rank = 5;

Output: 
Document Depend: 0;

however DENSE_RANK() will return each 4th and fifth highest wage appropriately as 3000 and 1000.

choose distinct wage from (
choose e.*, dense_rank() over (order by wage desc) as dense_rank 
from Worker e
) the place dense_rank = 4;

Output
SALARY
3000

and the fifth most can be:

choose distinct wage from (
choose e.*, dense_rank() over (order by wage desc) as dense_rank from Worker e
) the place dense_rank = 5;

Output:
SALARY
1000

That is all about calculate the second highest wage in Oracle utilizing ROWNUM, RANK() and DENSE_RANK() operate. Here’s a good abstract of the distinction between RANK, ROW_NUMBER and DENSE_RANK operate on your fast reference:

Additional Studying
Introduction to SQL
The Full SQL Bootcamp
SQL for Newbs: Information Evaluation for Inexperienced persons