RANK() Function
The RANK() function in SQL Server is a tool used to assign a position or rank to each row in a result set based on specific criteria. If two or more rows share the same values according to the specified criteria, they will receive the same rank. In this scenario, the rank of the next row is incremented by the number of tied rows. For example, if two rows tie for the first position, the next row will be assigned a rank of 3, not 2. This function is particularly valuable when you need to determine the relative position of rows in a sorted dataset. It’s extensively utilized in analytical queries to gain insights and perform calculations based on these rankings.
Syntax:
RANK() OVER (PARTITION BY column1, column2, ... ORDER BY sort_column1, sort_column2, ...)
- RANK(): The function itself.
- PARTITION BY: An optional clause that divides the result set into partitions or groups. 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 a 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,
RANK() OVER (ORDER BY Score DESC) AS Rank
FROM
Students;
Output:
Explanation:
- Both Jane and Alice have the highest score, so they share the top rank (1), and the next rank is 3.
- John and Mark both have the same score (85), so they share rank 3. The next rank is 5.
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.