Differnce between Rank() and Dense Rank()
|
Criteria |
Rank() |
Dense_Rank() |
|---|---|---|
|
Definition |
Assigns a unique rank to each row, leaving no gaps between ranks. If there are ties, the next rank will be skipped. |
Assigns a unique rank to each row, leaving no gaps between ranks. If there are ties, the next rank will not be skipped. |
|
Example |
Data: 10, 20, 20, 30, 40 Rank: 1, 2, 2, 4, 5 |
Data: 10, 20, 20, 30, 40 Dense Rank: 1, 2, 2, 3, 4 |
|
Behavior with ties |
Skips the next rank after a tie. |
Does not skip the next rank after a tie. |
|
Example with ties |
Data: 10, 20, 20, 30, 40 Rank: 1, 2, 2, 4, 5 |
Data: 10, 20, 20, 30, 40 Dense Rank: 1, 2, 2, 3, 4 |
|
Gaps between ranks |
Leaves gaps between ranks after ties. |
Does not leave gaps between ranks after ties. |
|
Example with gaps |
Data: 10, 20, 20, 30, 40 Rank: 1, 2, 2, 4, 5 |
Data: 10, 20, 20, 30, 40 Dense Rank: 1, 2, 2, 3, 4 |
|
Effect of skipping ranks |
May lead to non-sequential ranks. |
Always maintains sequential ranks. |
|
Application |
Useful when you want to differentiate between tied values distinctly. |
Useful when you want to maintain a sequential rank without gaps. |
|
Syntax |
RANK() OVER (PARTITION BY … ORDER BY …) |
DENSE_RANK() OVER (PARTITION BY … ORDER BY …) |
Rank and Dense Rank in SQL Server
The RANK() and DENSE_RANK() functions are essential tools in SQL Server for assigning rankings to rows in a dataset based on specified criteria. RANK() functions assign a unique rank to each distinct row in the result set, with tied rows receiving the same rank and leaving gaps in subsequent ranks. For example, if two rows tie for first place, the next row will receive a rank of 3.
On the other hand, DENSE_RANK() also assigns ranks based on criteria, but it does not leave gaps between ranks in case of tied rows. This means that if there are ties, the next rank will not skip any numbers. These functions are particularly useful when you need to identify the relative position of rows in a sorted dataset, which can be crucial for tasks like finding top performers or tracking trends over time. They offer valuable insights into the data and enable more sophisticated analysis and reporting.