Ranking Window Functions

The most commonly used ranking window functions are RANK(), DENSE_RANK(), and ROW_NUMBER().

• RANK() - The rank function, as its name implies, gives each row in a partition a rank. The first row is given rank 1, and subsequent rows with the same value are given the same rank. One rank value will be skipped for the rank that follows two identical rank values. Basically, we’ll skip as many rank values as there are identical rows in the column. We’ll take a look at an example later on.

• DENSE_RANK() - Similar to the RANK() function, the first row is given rank 1, and subsequent rows with the same value have the same rank. The difference between RANK() and DENSE RANK() is that in DENSE RANK(), no rank is skipped when there are two identical rows, i.e. two ranks that are the same. Again, the example should make things clearer.

• ROW_NUMBER() - It gives all the rows in the partition a sequence of consecutive numbers. No two rows can have the same row number within a division.

Imagine we want to calculate the row number, rank, and dense rank according to the country, by taking into consideration only the Sex and Country of the customer. Then, we have to run the following query:

1SELECT 
2ROW_NUMBER() OVER (PARTITION BY Country ORDER BY Sex) 
3AS RowNumber, Sex, Country,
4RANK() OVER(PARTITION BY Country
5ORDER BY Sex) AS Rank,
6DENSE_RANK() OVER(PARTITION BY Country
7                  ORDER BY Sex) 
8                  AS DenseRank,
9FROM Table1 

The resulting table would look like this: