Ranking Functions — Use ROW_NUMBER, RANK, and DENSE_RANK - SQL

SECTION 1

Historical Context & Motivation

For decades, SQL operated under a strict set-based paradigm in which every query either returned complete rows or collapsed them via aggregation with GROUP BY. If you needed to assign a sequential rank to each row — say, numbering students by GPA within each department — you were forced to write correlated subqueries or self-joins that were both verbose and computationally expensive. The relational model as E. F. Codd originally articulated it in 1970 did not provide a native concept for row-relative calculations because the model treats relations as unordered sets. This fundamental mismatch between the theoretical model and practical reporting needs created a persistent gap in the language.

The concept of window functions (sometimes called analytic functions) arose in the late 1990s as database researchers and vendors recognized that many analytical queries — running totals, moving averages, and rankings — could be expressed far more naturally with a construct that performs calculations across a 'window' of rows related to the current row without reducing the result set. This led to a formal specification in the SQL:2003 standard, although several major vendors had already begun shipping proprietary implementations.

1992 • SQL-92 Standard

The ANSI/ISO SQL-92 standard codifies GROUP BY aggregation but provides no mechanism for row-level ranking without subqueries or procedural extensions.

1999 • Oracle 8i Analytic Functions

Oracle introduces proprietary analytic functions including RANK, DENSE_RANK, and ROW_NUMBER, pioneering the window-function paradigm in commercial RDBMSs.

2003 • SQL:2003 Standard

The ISO SQL:2003 revision formally standardizes the OVER clause, PARTITION BY, ORDER BY within windows, and the three ranking functions ROW_NUMBER, RANK, and DENSE_RANK.

2012 • Broad Engine Adoption

By this year, PostgreSQL, SQL Server, MySQL (limited), and SQLite all support window functions, making them a portable, cross-platform tool for developers.

2018 • MySQL 8.0 Full Support

MySQL 8.0 ships full window-function support, closing the last major adoption gap and cementing ranking functions as a universal SQL capability.

The core question that ranking functions answer is deceptively simple: How do I assign an ordinal position to each row within a defined ordering, while preserving every row in the output? The three functions — ROW_NUMBER(), RANK(), and DENSE_RANK() — each answer that question with a subtly different treatment of ties, and understanding those differences is essential for writing correct analytical queries.

SECTION 2

Core Principles & Definitions

Before diving into syntax, it is important to internalize the foundational ideas that unify all three ranking functions. Each function operates over a window — a subset of rows defined by the OVER clause — and returns an integer value for every row in the result set. Unlike GROUP BY, window functions never reduce the number of output rows; they annotate each row with additional computed information. The PARTITION BY sub-clause optionally splits the window into independent groups (analogous to GROUP BY but without collapsing), while ORDER BY within the OVER clause determines the logical sequence used for ranking.

ROW_NUMBER()

Assigns a unique, sequential integer to each row within the partition, starting at 1. Ties are broken arbitrarily (non-deterministically) unless the ORDER BY clause is fully deterministic.

RANK()

Assigns the same integer to tied rows, then skips subsequent ranks. If two rows share rank 2, the next row receives rank 4 — producing gaps in the sequence.

DENSE_RANK()

Like RANK(), tied rows receive the same integer, but no ranks are skipped. If two rows share rank 2, the next distinct value receives rank 3 — producing a gap-free sequence.

PARTITION BY

An optional clause that divides the result set into independent partitions. Ranking restarts at 1 within each partition, enabling group-relative analysis.

ORDER BY (in OVER)

Defines the logical sort order used to determine rank. This is distinct from the query-level ORDER BY and controls only the ranking computation.

✦

KEY TAKEAWAY

Think of ranking functions like numbering runners crossing a finish line. ROW_NUMBER is a strict bib-number assignment: even if two runners cross at the exact same instant, the announcer calls one '2nd' and the other '3rd.' RANK gives both runners '2nd' place, but the next runner is called '4th' (skipping '3rd'). DENSE_RANK also gives both '2nd,' but the next runner is called '3rd' — no positions are wasted.

SECTION 3

Visual Explanation

The following diagram illustrates how the three ranking functions handle an identical data set of six rows ordered by score. Notice that the rows with tied scores (85) are the critical case that differentiates the three functions.

The yellow-highlighted 'tie zone' shows how Carol and Dave (both scoring 85) are handled. ROW_NUMBER assigns distinct values (3, 4). RANK assigns both 3 but skips to 5. DENSE_RANK assigns both 3 and continues with 4.

The diagram makes two critical properties visible. First, ROW_NUMBER always produces a permutation of {1, 2, …, n}, meaning no two rows within a partition ever share the same value. Second, RANK's maximum value always equals n (the number of rows), whereas DENSE_RANK's maximum equals the number of distinct sort-key values. These invariants are the key to choosing the right function for a given task.

SECTION 4

How the Functions Work Internally

Although ranking functions are specified declaratively in SQL, understanding their internal logic helps you predict behavior in edge cases. Conceptually, the query engine processes ranking in three phases: partitioning, sorting, and numbering. Below we formalize the numbering logic for each function.

General Syntax Template

SYNTAX TEMPLATE

<function>() OVER ( [PARTITION BY col₁, col₂, …] ORDER BY colₖ [ASC|DESC] )

Where <function> is ROW_NUMBER, RANK, or DENSE_RANK. PARTITION BY is optional; ORDER BY is required for meaningful results.

ROW_NUMBER Logic

ROW_NUMBER ASSIGNMENT

row_number(rᵢ) = i, where i ∈ {1, 2, …, n} is the position after sorting

Each row rᵢ in the sorted partition receives its ordinal position. If two rows have the same sort key, the DBMS breaks the tie arbitrarily, making the result non-deterministic unless the ORDER BY uniquely identifies rows.

RANK Logic

RANK ASSIGNMENT

rank(rᵢ) = 1 + |{ rⱼ : sort_key(rⱼ) < sort_key(rᵢ) }|

The rank of row rᵢ equals one plus the count of rows whose sort key is strictly less than rᵢ's sort key. This formula produces ties for equal keys and gaps after ties.

DENSE_RANK Logic

DENSE_RANK ASSIGNMENT

dense_rank(rᵢ) = |{ distinct sort_key(rⱼ) : sort_key(rⱼ) ≤ sort_key(rᵢ) }|

The dense rank of rᵢ equals the number of distinct sort-key values that are less than or equal to rᵢ's. This guarantees a contiguous integer sequence with no gaps.

⚠

Determinism Warning

When using ROW_NUMBER(), always ensure the ORDER BY clause is deterministic — typically by including a unique column (e.g., primary key) as a tiebreaker. Otherwise, the same query may return different numbering across executions, which can cause subtle bugs in pagination logic.

SECTION 5

Detailed Behavior Classification

Understanding when to choose each ranking function requires a clear mental model of their behavioral differences across several dimensions. The following diagram provides a decision-tree view, while the table below gives a comprehensive feature comparison.

Start at the top. If your data has no ties on the ORDER BY key, all three functions produce the same output. When ties exist, decide whether each tied row needs a unique number (ROW_NUMBER), a shared rank with gaps (RANK), or a shared rank with a contiguous sequence (DENSE_RANK).

Property	ROW_NUMBER()	RANK()	DENSE_RANK()
Uniqueness	Always unique within partition	Ties share same value	Ties share same value
Gaps after ties	N/A (no ties)	Yes — skips ranks	No — contiguous
Max value (n rows)	n	n	≤ n (# distinct keys)
Deterministic	Only with unique ORDER BY	Always	Always
Common use case	Pagination, dedup	Olympic / competition ranking	Top-N distinct values

Feature comparison of the three ranking functions

SECTION 6

Worked Example

Consider a table employees with columns emp_id, department, and salary. The task is to rank employees by salary within each department using all three functions, then select only the top-2 earners per department.

Ranking Employees by Salary Within Each Department

1
Step 1 — Write the Base Query with All Three Functions

We use a single SELECT to compute all three ranking columns simultaneously. The PARTITION BY department clause restarts ranking for each department, and ORDER BY salary DESC sorts from highest to lowest.

SELECT emp_id, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drnk FROM employees;
2
Step 2 — Examine the Output for a Department with Ties

Suppose the Engineering department has salaries: 120k, 110k, 110k, 95k. The three functions produce the following values for the tied 110k rows:

ROW_NUMBER → (1, 2, 3, 4) | RANK → (1, 2, 2, 4) | DENSE_RANK → (1, 2, 2, 3)
3
Step 3 — Filter to Top-2 Using a CTE

To select the top-2 earners, we wrap the ranking query in a Common Table Expression (CTE) and filter. The choice of function matters: DENSE_RANK() <= 2 returns all employees in the top two salary tiers (potentially more than 2 rows if ties exist), while ROW_NUMBER() <= 2 always returns exactly 2 rows per partition.

WITH ranked AS ( SELECT emp_id, department, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drnk FROM employees ) SELECT * FROM ranked WHERE drnk <= 2;
4
Step 4 — Interpret Results and Choose the Right Function

For Engineering, DENSE_RANK ≤ 2 returns three rows (the 120k and both 110k employees) because DENSE_RANK treats both 110k salaries as rank 2. If the business requirement is 'exactly 2 rows per department,' use ROW_NUMBER. If it is 'the top 2 salary levels,' DENSE_RANK is correct. If an Olympic-style report is needed where the reader should see the gap (no '3rd' place because two tied for 2nd), use RANK.

The correct function depends on the business requirement, not a universal rule.

SECTION 7

Strengths, Limitations & Common Pitfalls

Each ranking function excels in certain scenarios and introduces subtle hazards in others. The table below summarizes their strengths and limitations to guide production-quality SQL development.

Function	Strengths	Limitations / Pitfalls
`ROW_NUMBER()`	Guarantees unique values; ideal for pagination (OFFSET-free), deduplication via CTE + WHERE rn = 1, and generating surrogate sequences.	Non-deterministic when ORDER BY is not unique. May silently drop tied rows in top-N filters. Can mask data issues where ties should be surfaced.
`RANK()`	Correctly reflects positional ranking with gaps, matching real-world competition scoring. Deterministic regardless of ORDER BY uniqueness.	Gaps can complicate top-N filtering: WHERE rnk <= 3 might return fewer than 3 distinct rank values if many rows tie at rank 1. Users sometimes confuse the gap semantics.
`DENSE_RANK()`	Gap-free ranking simplifies top-N distinct value queries. Maximum value equals the count of distinct sort keys, which is often semantically meaningful.	Can return more rows than expected in top-N filters if many ties exist. Not suitable when you need a bijection between rows and integers.

Strengths and limitations of each ranking function

🎯

CHOOSING THE RIGHT FUNCTION

Think of it in terms of engineering trade-offs: ROW_NUMBER prioritizes cardinality guarantees (exactly N rows), RANK prioritizes positional accuracy (tells you how many rows are above you), and DENSE_RANK prioritizes tier counting (how many distinct levels exist above you). Match the function to the question being asked, not to a default habit.

SECTION 8

Connection to Advanced Window Function Theory

Ranking functions are just one family within the broader window-function ecosystem defined by the SQL standard. Mastering them opens the door to more powerful constructs such as NTILE (which divides rows into roughly equal buckets), LAG/LEAD (which access values from preceding or following rows), and frame specifications (ROWS BETWEEN … AND …) that enable sliding-window aggregations like moving averages and cumulative sums. Understanding the OVER clause semantics you learned with ranking functions transfers directly to every other window function.

Concept	Ranking Functions (This Lesson)	Advanced Window Functions
Output type	Integer rank per row	Aggregates, offsets, percentiles per row
Frame clause	Not used (entire partition)	ROWS/RANGE BETWEEN defines sliding window
Typical applications	Top-N queries, pagination, dedup	Running totals, moving averages, gap analysis
Performance consideration	Requires sort; O(n log n) per partition	Sort + frame scan; potentially O(n²) without optimization

Ranking functions vs. advanced window functions

From a performance perspective, ranking functions require the query engine to sort each partition by the ORDER BY key, making index design critical. A covering index on (partition_col, sort_col) can eliminate expensive in-memory sorts. In distributed databases (e.g., BigQuery, Spark SQL), PARTITION BY determines data shuffling, so choosing narrow partitions reduces network overhead. As you advance, you will find that the mental model of 'partition → sort → compute per row' applies uniformly across all window functions, making ranking functions an ideal entry point into this powerful family of SQL constructs.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

A table has 10 rows partitioned into a single group and ordered by a column where 4 rows share the same value. What is the maximum value returned by RANK() and by DENSE_RANK()? Explain why they differ.

Show answer

RANK()'s maximum value is always equal to the number of rows in the partition, which is 10. This is because RANK counts how many rows precede the current row (including ties), so the last row's rank accounts for all 9 rows before it. DENSE_RANK()'s maximum equals the number of distinct sort-key values. With 10 rows and 4 sharing one value, there are at most 7 distinct values (6 unique + 1 shared by 4), so the maximum DENSE_RANK is 7. The fundamental difference is that RANK preserves positional count while DENSE_RANK preserves tier count.

Question 2: BASIC CALCULATION

Write a query that assigns a row number to each order in an orders table, ordered by order_date DESC, partitioned by customer_id. Then use it to retrieve only the most recent order per customer.

Show answer

Use a CTE with ROW_NUMBER:

WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) AS rn FROM orders) SELECT * FROM cte WHERE rn = 1;

Note the inclusion of order_id as a tiebreaker in ORDER BY to ensure determinism. ROW_NUMBER is the correct choice here because we want exactly one row per customer, and ties should be broken rather than duplicated. RANK or DENSE_RANK could return multiple rows if two orders share the same date.

Question 3: INTERMEDIATE

Given a students table with columns (student_id, department, gpa), write a query to find all students whose GPA is in the top 3 distinct GPA tiers within their department. Which ranking function should you use, and why would the others be incorrect for this requirement?

Show answer

Use DENSE_RANK:

WITH ranked AS (SELECT *, DENSE_RANK() OVER (PARTITION BY department ORDER BY gpa DESC) AS drnk FROM students) SELECT * FROM ranked WHERE drnk <= 3;

DENSE_RANK is correct because the requirement says 'top 3 distinct GPA tiers,' which maps directly to DENSE_RANK's gap-free counting of distinct values. ROW_NUMBER would limit results to exactly 3 rows per department, potentially excluding tied students. RANK could return fewer than 3 tiers in the filter if, say, 5 students tied for rank 1 — then rank 2 would actually be 6, and a WHERE rnk <= 3 filter would return only those 5 tied students.

Question 4: APPLIED

You are building a REST API that returns paginated results (20 rows per page) from a products table sorted by price DESC. Many products share the same price. Write a query for page 3 (rows 41–60) and explain why your choice of ranking function prevents duplicate or missing rows across pages.

Show answer

Use ROW_NUMBER with a deterministic tiebreaker:

WITH paged AS (SELECT *, ROW_NUMBER() OVER (ORDER BY price DESC, product_id ASC) AS rn FROM products) SELECT * FROM paged WHERE rn BETWEEN 41 AND 60;

ROW_NUMBER is the only correct choice for pagination because it guarantees a bijection between row numbers and result rows — every integer from 1 to n appears exactly once. Adding product_id as a secondary sort key ensures determinism so that the same row always appears on the same page regardless of execution plan. RANK or DENSE_RANK would be incorrect because tied prices would share the same rank, causing some rows to appear on multiple pages (or no page) when filtering by rank range.

Question 5: CRITICAL THINKING

Prove or disprove: for any data set and ORDER BY clause, DENSE_RANK(rᵢ) ≤ RANK(rᵢ) ≤ ROW_NUMBER(rᵢ) for every row rᵢ. Under what condition do all three functions return identical results for every row?

Show answer

The inequality DENSE_RANK(rᵢ) ≤ RANK(rᵢ) ≤ ROW_NUMBER(rᵢ) holds universally for every row rᵢ within any partition. For the first inequality: DENSE_RANK counts distinct sort-key values ≤ the current key, while RANK counts total rows with strictly smaller keys plus 1. Since each distinct key group contributes ≥ 1 row, the count of preceding rows ≥ the count of preceding distinct keys, so RANK ≥ DENSE_RANK. For the second inequality: ROW_NUMBER assigns a unique integer starting at 1, and RANK(rᵢ) = 1 + |rows with smaller key|. Since ROW_NUMBER of rᵢ = 1 + |all preceding rows| ≥ 1 + |rows with strictly smaller key| = RANK(rᵢ). All three functions return identical results for every row if and only if the ORDER BY clause induces a total order with no ties — that is, every row has a distinct sort-key value.

SUMMARY

Lesson Summary

Summary

SQL's three ranking functions — ROW_NUMBER(), RANK(), and DENSE_RANK() — all operate via the OVER clause with optional PARTITION BY and required ORDER BY. They differ only in how they handle ties: ROW_NUMBER always produces unique integers (non-deterministic on ties), RANK assigns equal values to ties but introduces gaps, and DENSE_RANK assigns equal values to ties with a contiguous sequence.

The invariant DENSE_RANK ≤ RANK ≤ ROW_NUMBER holds for every row. Choose ROW_NUMBER for pagination and deduplication (where you need exactly N rows), RANK for competition-style standings (where gaps after ties are meaningful), and DENSE_RANK for top-N distinct tier queries (where you care about the number of unique levels). These functions form the foundation of SQL window function literacy and transfer directly to more advanced constructs like LAG, LEAD, NTILE, and frame-based aggregations.