DENSE_RANK() Function
The DENSE_RANK() function in SQL server serves the purpose of assigning ranks to rows in a dataset according to specific conditions. Much like the RANK() function, it orders the data based on certain criteria. However, what sets it apart is that it ensures there are no gaps between ranks in cases where multiple rows share the same values. This means that tied rows receive consecutive ranks without any interruptions. DENSE_RANK() is particularly useful when you need a continuous and unbroken sequence of ranks. It’s especially valuable in situations where you require a clear and uninterrupted ordering of data, particularly when dealing with tied values. This function is widely employed in scenarios where a seamless and sequential arrangement of data is essential for accurate analysis and reporting.
Syntax:
DENSE_RANK() OVER (PARTITION BY column1, column2, ... ORDER BY sort_column1, sort_column2, ...)
- DENSE_RANK(): The function itself.
- PARTITION BY: This optional clause divides the result set into partitions or groups, and the ranking is applied within each partition separately. If omitted, the entire result set is treated as a single partition.
- ORDER BY: This clause specifies the columns by which the data is sorted to determine the ranking order.
Example:
Let’s assume we have same table called ‘Students’ with the following data. In this table, Alice has a score of 90.
Name |
Score |
John |
85 |
Jane |
90 |
Mark |
85 |
Alice |
90 |
SELECT
Name,
Score,
DENSE_RANK() OVER (ORDER BY Score DESC) AS Dense_Rank
FROM
Students;
Output:
Explanation:
- Both Alice and Jane have the highest score (90), so they share the top dense rank (1).
- John and Mark both have the same score (85), so they share dense rank 2. The next dense rank is 3.
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.