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()vsROW_NUMBER()vsDENSE_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
Note
Interview Question: What happens after a tie in RANK()?
RANK() leaves gaps after ties. If 3 rows tie for rank 2, ranks will be [1, 2, 2, 2, 5]. Rank 3 and 4 are skipped.
Interactive Challenges
Summary
RANK() assigns shared ranks to tied values and skips subsequent numbers, leaving rank gaps.
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.