RANK() Function in MS SQL Server with Examples

The RANK() function is one of the ranking functions in T-SQL used to assign a rank to each row within a partition of a result set. It is especially useful for analytics and reporting scenarios.

✅ Syntax

RANK() OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression [ASC|DESC]
)
  

• PARTITION BY: (Optional) Divides the result set into partitions.
• ORDER BY: Defines the order of the rows for ranking.

📘 Key Characteristics

• Rows with equal values receive the same rank.
• The next rank(s) are skipped based on the number of ties.

Example: Ranks: 1, 2, 2, 4, 5 (note how rank 3 is skipped)

🧪 Example Table: Sales

📊 Example 1: RANK() by Sales (No Partitioning)

SELECT
    EmployeeName,
    SalesAmount,
    RANK() OVER (ORDER BY SalesAmount DESC) AS SalesRank
FROM Sales;
  

🔽 Output

Note: Bob and Carol share rank 3. Rank 4 is skipped.

📊 Example 2: RANK() with PARTITION BY Region

SELECT
    EmployeeName,
    Region,
    SalesAmount,
    RANK() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS RegionalRank
FROM Sales;
  

🔽 Output

Note: Ranks restart within each region using PARTITION BY.

🔄 Difference Between RANK(), DENSE_RANK(), and ROW_NUMBER()

🧠 When to Use RANK()

• When you want to allow ties and skip ranks appropriately.
• Useful for leaderboards, contests, or percentile grouping.
• Can be used with pagination or top-N filtering per category.

✅ Summary

• RANK() is useful for ordered results with possible ties.
• PARTITION BY helps reset ranks across logical groups.
• Be mindful of skipped rankings when values are tied.

In the above example, we have seen how to use RANK() Function in MS SQL Server.

I have an another post MS SQL Server String Related Functions where you will learn about different rank related functions of MS SQL Server.

Hope you have enjoyed the tutorial. If you want to get updated, like the facebook page http://www.facebook.com/LearningBigDataAnalytics and stay connected.