Overview of RANK() in sql server

General
1

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 and 102 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 the SalesAmount 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 the IT 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 and 102 both have the same SalesAmount (5000), so they both receive rank 1.
  • The next employee, 103, with a SalesAmount 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 unlike RANK(), 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, unlike RANK().

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, while ROW_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.