RANK

Overview

RANK() assigns a rank to each row within a partition. Identical values receive equal ranks, leaving gaps in the sequence for subsequent ranks.

Learning Objectives

• Calculate position rankings using RANK().
• Analyze rank behavior when handling tied values (gaps).
• Compare RANK() vs ROW_NUMBER() vs DENSE_RANK().

Detailed Concept Explanation

When ranking items:

• If two rows tie for 1st place, both get RANK = 1.
• The next row will receive RANK = 3 (leaving a gap for rank 2).

Sequence Comparison

| Values | ROW_NUMBER | RANK | DENSE_RANK | | :--- | :--- | :--- | :--- | | 100 | 1 | 1 | 1 | | 100 | 2 | 1 | 1 | | 80 | 3 | 3 (gap!) | 2 | | 70 | 4 | 4 | 3 |

Code Examples

SQL

-- Rank employees by salary (handling ties with rank gaps)
SELECT 
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS sal_rank
FROM employees;

Best Practices

• Choose the Right Ranking Function: Use RANK() when ties should share a rank and subsequent ranks should reflect total item counts (leaving gaps).

Interview Perspective

Interactive Challenges

Summary

RANK() assigns shared ranks to tied values and skips subsequent numbers, leaving rank gaps.