Introduction
In SQL, ranking functions are powerful tools that allow you to assign unique rankings to rows within a partition of a result set. These functions are particularly useful in scenarios where you need to identify the order of rows based on specific criteria. The three primary ranking functions are RANK(), DENSE_RANK(), and ROW_NUMBER(). While they may seem similar, they have distinct behaviors, especially when it comes to handling ties. In this article, we'll explore these functions in detail and demonstrate their differences with a practical example.
RANK()
The RANK() function assigns a unique rank to each row within a partition of a result set, with gaps in the ranking sequence where there are ties. This means that if two or more rows have the same value in the ordering column(s), they will be assigned the same rank, but the next rank will be incremented by the number of tied rows.
Syntax
RANK() OVER (
PARTITION BY column1, column2, ...
ORDER BY column1, column2, ...
)
Example
SELECT
Name,
Score,
RANK() OVER (ORDER BY Score DESC) AS Rank
FROM
Students;
Result
| Name | Score | Rank |
|---------|-------|------|
| Alice | 95 | 1 |
| Bob | 85 | 2 |
| Charlie | 85 | 2 |
| Dave | 75 | 4 |
| Eve | 70 | 5 |
In this example, Bob and Charlie have the same score and are both ranked 2nd. The next rank, assigned to Dave, is 4th, leaving a gap at rank 3.
DENSE_RANK()
The DENSE_RANK() function is similar to RANK() but without gaps in the ranking sequence. When rows have the same value in the ordering column(s), they receive the same rank, but the next rank is incremented by one, regardless of the number of ties.
Syntax
DENSE_RANK() OVER (
PARTITION BY column1, column2, ...
ORDER BY column1, column2, ...
)
Example
SELECT
Name,
Score,
DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank
FROM
Students;
Result
| Name | Score | DenseRank |
|---------|-------|-----------|
| Alice | 95 | 1 |
| Bob | 85 | 2 |
| Charlie | 85 | 2 |
| Dave | 75 | 3 |
| Eve | 70 | 4 |
Here, Bob and Charlie are both ranked 2nd, but the next rank is 3rd, assigned to Dave, with no gaps in the ranking sequence.
ROW_NUMBER()
The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition, without considering ties. Each row gets a distinct number, even if there are ties in the ordering column(s).
Syntax
ROW_NUMBER() OVER (
PARTITION BY column1, column2, ...
ORDER BY column1, column2, ...
)
Example
SELECT
Name,
Score,
ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNum
FROM
Students;
Result
| Name | Score | RowNum |
|---------|-------|--------|
| Alice | 95 | 1 |
| Bob | 85 | 2 |
| Charlie | 85 | 3 |
| Dave | 75 | 4 |
| Eve | 70 | 5 |
In this example, even though Bob and Charlie have the same score, they are assigned unique row numbers 2 and 3, respectively.
Summary
- RANK(): Leaves gaps after ties.
- DENSE_RANK(): Does not leave gaps after ties.
- ROW_NUMBER(): Ignores ties and assigns a unique number to each row.
Understanding these differences is crucial for effectively utilizing ranking functions in SQL to achieve the desired results in your queries.
Please consider liking and following me for more articles and if you find this content helpful.