Distinctive Analytics

There are some important SQL functions we can study, like

• RANK()
• DENSERANK()
• OVER()
• PARTITION()

RANK() and DENSE_RANK() are both effort-saving and effective functions to learn in SQL. The RANK() the function simply works like giving rank 1 to the student who earns the maximum marks in the exam, giving rank 2 to the student who scores the second maximum marks in the exam, and so on.

Let’s understand the details with the following examples: Suppose the current position of football-playing countries in the FIFA ranking system is as given in the table.

The second column in this table displays the total points earned by each country in the FIFA ranking system. Now we want to apply functions RANK() and DENSE_RANK() on the basis of total points.

We can see that Brazil and Belgium have the same number of total points.

Simple Implementation

SELECT *,
RANK () OVER( ORDER BY TOTAL POINTS asc) as RANK
FROM ‘FIFA_table’
ORDER BY RANK;

Countries that have equal total points are given the same rank, and for countries that have fewer adjacent points, the rank will be given after considering the positions of previous rank holders.

Applying partition

We can also apply RANK() category- or division-wise. This can be easily understood from the following example, where we will be giving a ranking to the dataset according to each division or region in which football-playing countries are categorized, viz; UEFA, CONMEBOL.

Suppose we have this table.

1. RANK()

SELECT *,
RANK () OVER( PARTITION by Division ORDER by Total Points asc) as RANK
FROM ‘FIFA_table’
ORDER by RANK;

After applying the code for RANK() with PARTITION() of any category.

2. DENSE_RANK()

DENSE_RANK() will rank data, but it does not take into account the position of other rank holders. This can be seen in the following example.

SELECT *,
DENSE_RANK () OVER( ORDER BY TOTAL POINTS asc) as RANK
FROM ‘FIFA_table’
ORDER BY RANK;

Countries that have equal total points are given the same rank, the same rank, for rest of countries ranks will be given according to total points. The adjacent ranks will be given to countries without considering the row positions of previous rank holders.

In simple words, Brazil and Belgium have equal total points, which is the highest number of total points in the table, so they are given rank 1. France has the second-highest total of points in the table, so it is given rank 2, despite being in row position 3. 

This is contrary to RANK() and that’s why it explains the difference between RANK() and DENSE_RANK().