When working with SQL, there are times when we need to assign a ranking to rows within a result set based on a specific ordering. This is where the RANK() window function comes in handy. In this article, I’ll explain how RANK() works in SQL, show some practical examples, and highlight the key differences between RANK() and similar functions like DENSE_RANK() and ROW_NUMBER().
What is RANK() in SQL?
The RANK() function is a window function that assigns a unique rank to each row within a partition of a dataset based on a specified order. If two or more rows have the same value, they receive the same rank, and the next rank is skipped accordingly.
Basic Syntax of RANK()
The syntax for the RANK() function is straightforward:
RANK() OVER (PARTITION BY column_name ORDER BY column_name)Breaking it down:
- PARTITION BY: This is optional. It divides the result set into partitions where
RANK()is applied separately. If omitted, the function is applied to the entire result set. - ORDER BY: Determines the ordering of rows within each partition before the
RANK()function assigns a rank.
Example Dataset
To fully understand the function, let’s assume we have a table named Sales with the following data:
| SalesID | Employee | Region | SalesAmount |
|---|---|---|---|
| 1 | Alice | East | 5000 |
| 2 | Bob | East | 7000 |
| 3 | Charlie | East | 7000 |
| 4 | David | West | 6000 |
| 5 | Emma | West | 8000 |
Using RANK() to Rank Sales Amounts
If we want to rank employees based on their sales amount within each region, using RANK() would look like this:
SELECT Employee, Region, SalesAmount,
RANK() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS Rank
FROM Sales;The result set would look like this:
| Employee | Region | SalesAmount | Rank |
|---|---|---|---|
| Bob | East | 7000 | 1 |
| Charlie | East | 7000 | 1 |
| Alice | East | 5000 | 3 |
| Emma | West | 8000 | 1 |
| David | West | 6000 | 2 |
Why Does RANK() Skip Numbers?
Notice how Bob and Charlie, who have the same sales amount, share rank 1, but the next rank is 3 instead of 2. This happens because RANK() assigns the same number to duplicates and doesn’t increment the rank sequentially.
Difference Between RANK(), DENSE_RANK(), and ROW_NUMBER()
Understanding how RANK() differs from similar ranking functions is essential:
- RANK(): Assigns the same rank to duplicate values and skips numbers.
- DENSE_RANK(): Assigns the same rank to duplicate values but doesn’t leave gaps.
- ROW_NUMBER(): Assigns a unique number to each row, ignoring duplicates.
Example Comparison
Using the same dataset, let’s compare the outputs:
SELECT Employee, Region, SalesAmount,
RANK() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS Rank,
DENSE_RANK() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS DenseRank,
ROW_NUMBER() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS RowNumber
FROM Sales;The output would be:
| Employee | Region | SalesAmount | Rank( ) | Dense_Rank( ) | Row_Number( ) |
|---|---|---|---|---|---|
| Bob | East | 7000 | 1 | 1 | 1 |
| Charlie | East | 7000 | 1 | 1 | 2 |
| Alice | East | 5000 | 3 | 2 | 3 |
| Emma | West | 8000 | 1 | 1 | 1 |
| David | West | 6000 | 2 | 2 | 2 |
Final Thoughts
The RANK() function is incredibly useful when dealing with ordered data, especially when identifying top performers, percentile rankings, or competitions where ties exist. Understanding its behavior and how it differs from DENSE_RANK() and ROW_NUMBER() ensures that you choose the right function for each scenario.
Other interesting article:
How PIVOT works in SQL? Best PIVOT examples