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.
|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()
select s.*, rank() over (order by Marks desc) “State Rank” from Scores s;
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
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.
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.