In SQL Server, the RANK()
function is part of the set of window functions that allows you to assign a unique rank to each row within a result set, based on the values of one or more columns. It is commonly used in scenarios where you need to assign sequential rankings to rows, such as sorting results and generating rank-based calculations for reports.
The RANK()
function is primarily used to give a rank to rows within a result set, but with specific behavior around handling ties (duplicate values). It assigns the same rank to rows that have identical values, but skips the next rank(s). For example, if two rows are tied for rank 1, the next row will be ranked 3 (not 2).
Syntax of the RANK()
Function
RANK() OVER (PARTITION BY <partition_expression> ORDER BY <order_expression>)
-
PARTITION BY <partition_expression>
(optional): This clause divides the result set into partitions (groups) to rank the rows within each group independently. If omitted, the entire result set is treated as a single partition. -
ORDER BY <order_expression>
: Defines the order of rows within each partition (or the entire result set) to assign ranks. The ordering could be in ascending or descending order, depending on the requirement.
Key Points:
- Ranking: The function assigns a unique rank to each row. If two rows have the same value in the
ORDER BY
clause, they receive the same rank. - Handling Ties: In case of ties, the
RANK()
function skips subsequent ranks. For example, if two rows are ranked 1, the next row will receive rank 3, not 2. - Return Value: The result of
RANK()
is an integer indicating the rank of the row in the specified order.
Example 1: Basic Usage of RANK()
Here is an example where we assign ranks based on sales amounts:
SELECT
EmployeeID,
SalesAmount,
RANK() OVER (ORDER BY SalesAmount DESC) AS Rank
FROM
Sales;
Explanation:
- This query ranks the employees based on the
SalesAmount
in descending order. - The employee with the highest
SalesAmount
gets rank 1, the second-highest gets rank 2, and so on. - If two employees have the same
SalesAmount
, they will share the same rank, and the next rank will be skipped.
Sample Output:
EmployeeID | SalesAmount | Rank |
---|---|---|
101 | 5000 | 1 |
102 | 5000 | 1 |
103 | 3000 | 3 |
104 | 2000 | 4 |
In this example:
- Employees
101
and102
have the same sales amount (5000), so they both receive rank 1. - Employee
103
, with a sales amount of 3000, gets rank 3 because the next available rank after the tie is skipped.
Example 2: Using PARTITION BY
You can partition the result set into groups (e.g., regions, departments) and rank within each group separately.
SELECT
EmployeeID,
Department,
SalesAmount,
RANK() OVER (PARTITION BY Department ORDER BY SalesAmount DESC) AS Rank
FROM
Sales;
Explanation:
- In this query, the data is partitioned by the
Department
column. - The
RANK()
function assigns ranks within each department based on theSalesAmount
in descending order. - Employees within each department will have their ranks reset to 1, based on their sales amount.
Sample Output:
EmployeeID | Department | SalesAmount | Rank |
---|---|---|---|
101 | HR | 5000 | 1 |
102 | HR | 3000 | 2 |
103 | IT | 4000 | 1 |
104 | IT | 2500 | 2 |
In this example:
- Employees in the
HR
department are ranked independently from those in theIT
department. - Within each department, employees are ranked by their
SalesAmount
in descending order.
Example 3: Tied Ranks
Let’s say we have employees with the same sales amount, and we want to see how RANK()
behaves with ties.
SELECT
EmployeeID,
SalesAmount,
RANK() OVER (ORDER BY SalesAmount DESC) AS Rank
FROM
Sales;
Sample Data:
EmployeeID | SalesAmount |
---|---|
101 | 5000 |
102 | 5000 |
103 | 3000 |
104 | 2000 |
Result:
EmployeeID | SalesAmount | Rank |
---|---|---|
101 | 5000 | 1 |
102 | 5000 | 1 |
103 | 3000 | 3 |
104 | 2000 | 4 |
- Employees
101
and102
both have the sameSalesAmount
(5000), so they both receive rank 1. - The next employee,
103
, with aSalesAmount
of 3000, is ranked 3 because the next rank (2) is skipped due to the tie.
Comparison with Other Ranking Functions
RANK()
: As mentioned earlier, this function skips ranks in case of ties. It gives the same rank to tied rows but leaves gaps in the subsequent ranks.DENSE_RANK()
: This function also assigns the same rank to tied rows, but unlikeRANK()
, it does not skip ranks. If two rows are tied at rank 1, the next row gets rank 2.ROW_NUMBER()
: This function assigns a unique number to each row, regardless of ties, ensuring no duplicate ranks. It doesn’t skip any numbers, even in the case of ties.
Example of DENSE_RANK()
vs RANK()
:
EmployeeID | SalesAmount | Rank | DenseRank |
---|---|---|---|
101 | 5000 | 1 | 1 |
102 | 5000 | 1 | 1 |
103 | 3000 | 3 | 2 |
104 | 2000 | 4 | 3 |
- As shown,
DENSE_RANK()
does not skip the next rank, unlikeRANK()
.
Use Cases for RANK()
- Leaderboard: To rank users, sales representatives, or athletes based on scores, sales, or performance.
- Reporting: To show ranked data, such as top N performers in sales, highest-rated products, etc.
- Windowed Aggregations: When you need rankings alongside other aggregates (e.g., top 10 products in each region).
Summary
- The
RANK()
function is a window function that assigns a rank to each row in the result set based on an ordering condition. - It handles ties by assigning the same rank to rows with equal values, but skips subsequent ranks.
PARTITION BY
allows you to rank within specific groups (e.g., departments, regions).- Compared to
DENSE_RANK()
,RANK()
creates gaps in ranking for tied values, whileROW_NUMBER()
provides a unique number for each row regardless of ties.
By using the RANK()
function in SQL Server, you can effectively rank your data in reports, leaderboards, or analytics without complex manual computations.