Rank(), Dense_Rank() and Row_Number() SQL

Let’s see below example,

Consider our education department has below data and they wanted to find the ranks of students State wise-Z, School wise-Y and Class wise-X.

Wonder how to achieve this in single SQL.

Name School Class Marks Class Rank
X
School Rank
Y
State Rank
Z
Alice Central School A Division 456 1 1 2
Bob Central School A Division 345 2 4 7
Charlie Central School B Division 401 2 3 5
Eve Central School B Division 445 1 2 3
Mallet Liberty School A Division 323 2 4 8
Oscar Liberty School A Division 433 1 2 4
Peggy Liberty School B Division 401 2 3 5
Sybil Liberty School B Division 467 1 1 1

We can achieve this using Rank(), Group by and Partition by concept.

Assume table name as “Scores”

1. Let’s starts with State wise rank for each student

To find State rank we have to order the students marks in descending order and call rank()
query:
select s.*, rank() over (order by Marks desc) “State Rank” from Scores s;

Simple right!!!

2. Let’s see how to find School wise rank for each student

Since multiple school data is in the same table “Scores” we have to partition data first based on school and  then order the Marks in descending order
query:
select s.*, rank() over (partition by School order by Marks desc) “School Rank” from Scores s;

3. Let’s see how to find Class wise rank for each student

It’s very similar to above, here we have to classify data based on School&Class first then order Marks by descending order.
query:
select s.*, rank() over (partition by School, Class order by Marks desc) “Class Rank” from Scores s;

In single query:
select s.*, rank() over (order by Marks desc) “State Rank”, rank() over (partition by School order by Marks desc) “School Rank”, rank() over (partition by School, Class order by Marks desc) “Class Rank” from Scores s;

Note: Marks column should be a number or integer field, if not use to_number(Marks) method then apply order by

We have two common methods like rank(), they are dense_rank() and row_number()

a. dense_rank() – Have you noticed in above example Charlie and Peggy  have the same score and so share same State Rank(5th rank). Due to this 6th rank is skipped and Bob got (7th rank). If you do not want to skip 6th rank use dense_rank() instead of rank().

b. row_number() – In the above example we saw Charlie and Peggy have same score and shared same rank. If we use row_number() one may get 5th and other may get 6th based on the order in which they appear in the query output, since row number just assign the sequential value based on some alphabetical order. This should not be used if you want to find the correct rank.

 

 

 

Advertisements

6 thoughts on “Rank(), Dense_Rank() and Row_Number() SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s