ROW_NUMBER, RANK, DENSE_RANK in PostgreSQL - Deep Dive

Overview - ROW_NUMBER, RANK, DENSE_RANK

What is it?

ROW_NUMBER, RANK, and DENSE_RANK are functions in SQL that assign numbers to rows in a result set based on their order. They help you sort and number rows when you want to find positions like first, second, or tied ranks. Each function handles ties differently, giving you control over how to number rows with the same values. These functions are useful for ranking, pagination, and grouping results.

Why it matters

Without these ranking functions, it would be hard to assign positions or ranks to rows in a sorted list, especially when multiple rows share the same value. This makes tasks like finding top performers, paginating results, or handling ties complicated and error-prone. These functions simplify such tasks, making data analysis and reporting clearer and more reliable.

Where it fits

Before learning these, you should understand basic SQL SELECT queries, ORDER BY clauses, and simple functions. After mastering these ranking functions, you can explore window functions more broadly, advanced analytics queries, and performance tuning for large datasets.

Mental Model

Core Idea

ROW_NUMBER, RANK, and DENSE_RANK assign position numbers to rows in a sorted list, differing only in how they handle ties.

Think of it like...

Imagine a race where runners finish at different times. ROW_NUMBER gives each runner a unique finishing position, even if they tie. RANK gives tied runners the same position but leaves gaps after them. DENSE_RANK also gives tied runners the same position but does not leave gaps.

Sorted Rows by Score:
┌─────────┬───────────┬──────────────┐
│ Position│ Score     │ Function     │
├─────────┼───────────┼──────────────┤
│ 1       │ 100       │ ROW_NUMBER=1 │
│ 2       │ 90        │ ROW_NUMBER=2 │
│ 3       │ 90        │ ROW_NUMBER=3 │
│ 4       │ 80        │ ROW_NUMBER=4 │

Ranking:
┌─────────┬───────────┬──────────────┐
│ Position│ Score     │ RANK         │
├─────────┼───────────┼──────────────┤
│ 1       │ 100       │ 1            │
│ 2       │ 90        │ 2            │
│ 3       │ 90        │ 2            │
│ 4       │ 80        │ 4            │

Dense Ranking:
┌─────────┬───────────┬──────────────┐
│ Position│ Score     │ DENSE_RANK   │
├─────────┼───────────┼──────────────┤
│ 1       │ 100       │ 1            │
│ 2       │ 90        │ 2            │
│ 3       │ 90        │ 2            │
│ 4       │ 80        │ 3            │

Build-Up - 7 Steps

1

Foundation

Understanding Basic Row Ordering

Concept: Learn how SQL orders rows using ORDER BY to prepare for ranking.

In SQL, ORDER BY sorts rows by one or more columns. For example, ORDER BY score DESC sorts rows from highest to lowest score. This sorting is the foundation for assigning ranks or numbers to rows.

Result

Rows are sorted in the desired order, ready for numbering or ranking.

Knowing how ORDER BY works is essential because ranking functions depend on the order of rows to assign correct positions.

2

Foundation

Introducing ROW_NUMBER Function

Concept: ROW_NUMBER assigns a unique sequential number to each row in the sorted result.

ROW_NUMBER() OVER (ORDER BY score DESC) numbers rows starting at 1 for the highest score, increasing by 1 for each row, even if scores tie. For example, two rows with score 90 get different numbers like 2 and 3.

Result

Each row has a unique number reflecting its position in the sorted list.

ROW_NUMBER guarantees unique numbering, which is useful when you want a strict order without ties.

3

Intermediate

Using RANK to Handle Ties

🤔Before reading on: do you think RANK skips numbers after ties or not? Commit to your answer.

Concept: RANK assigns the same rank to tied rows but leaves gaps in numbering after ties.

RANK() OVER (ORDER BY score DESC) gives tied rows the same rank number. If two rows tie for rank 2, the next rank will be 4, skipping 3. This reflects the idea that the tied rows share a position, and the count jumps accordingly.

Result

Tied rows share the same rank, and gaps appear in the ranking sequence after ties.

Understanding that RANK leaves gaps helps interpret rankings where ties affect subsequent positions, like in competitions.

4

Intermediate

Exploring DENSE_RANK Without Gaps

🤔Before reading on: does DENSE_RANK leave gaps after ties like RANK? Commit to your answer.

Concept: DENSE_RANK assigns the same rank to tied rows but does not leave gaps in numbering after ties.

DENSE_RANK() OVER (ORDER BY score DESC) gives tied rows the same rank number, but the next rank continues immediately without skipping. For example, if two rows tie at rank 2, the next rank is 3, not 4.

Result

Tied rows share the same rank, and ranking numbers are consecutive without gaps.

Knowing DENSE_RANK avoids gaps is important when you want a compact ranking sequence that reflects ties but keeps numbering tight.

5

Intermediate

Comparing ROW_NUMBER, RANK, and DENSE_RANK

🤔Before reading on: which function would you use to get unique row numbers despite ties? Commit to your answer.

Concept: Understand the differences in how each function handles ties and numbering.

ROW_NUMBER always gives unique numbers, ignoring ties. RANK gives the same number to ties but skips numbers after. DENSE_RANK gives the same number to ties without skipping. Choosing depends on whether you want unique positions or tied ranks with or without gaps.

Result

Clear understanding of when to use each function based on tie handling.

Recognizing these differences prevents mistakes in ranking logic and ensures correct interpretation of results.

6

Advanced

Using PARTITION BY with Ranking Functions

🤔Before reading on: do you think PARTITION BY restarts numbering for each group or continues globally? Commit to your answer.

Concept: PARTITION BY divides rows into groups, and ranking functions restart numbering within each group.

Using ROW_NUMBER() OVER (PARTITION BY category ORDER BY score DESC) numbers rows separately within each category. This is like having multiple races, one per category, each with its own ranking.

Result

Ranking numbers reset for each partition group, allowing grouped rankings.

Understanding PARTITION BY enables complex queries that rank within groups, a common real-world need.

7

Expert

Performance and Edge Cases in Ranking Functions

🤔Before reading on: do you think ranking functions always perform well on large datasets? Commit to your answer.

Concept: Ranking functions can be costly on large data and behave differently with NULLs or complex ORDER BY expressions.

Ranking functions require sorting, which can be slow on big tables without indexes. NULL values are treated as lowest by default but can be controlled. Also, using multiple columns in ORDER BY affects tie detection. Understanding these helps optimize queries and avoid surprises.

Result

Better performance and correct results when using ranking functions in production.

Knowing internal behavior and performance trade-offs helps write efficient, reliable ranking queries.

Under the Hood

Ranking functions are window functions that process rows in a defined order. Internally, the database sorts the rows according to the ORDER BY clause inside the OVER() clause. Then, it assigns numbers based on the function's logic: ROW_NUMBER increments for every row; RANK assigns the same number to ties and skips numbers after; DENSE_RANK assigns the same number to ties but continues numbering without gaps. Partitioning divides the data into groups, and numbering restarts for each group. The database engine uses sorting algorithms and memory buffers to handle this efficiently.

Why designed this way?

These functions were designed to simplify common ranking and numbering tasks in SQL without complex self-joins or subqueries. The different tie-handling methods reflect real-world ranking needs: unique numbering (ROW_NUMBER), competition-style ranking with gaps (RANK), and compact ranking without gaps (DENSE_RANK). This design balances expressiveness and performance, avoiding the need for manual calculations.

Input Rows
  │
  ▼
[Sort by ORDER BY]
  │
  ▼
[Partition by PARTITION BY (optional)]
  │
  ▼
[Assign Numbers]
  ├─ ROW_NUMBER: unique increment per row
  ├─ RANK: same number for ties, gaps after
  └─ DENSE_RANK: same number for ties, no gaps
  │
  ▼
Output Rows with Ranking

Myth Busters - 4 Common Misconceptions

Quick: Does ROW_NUMBER assign the same number to tied rows? Commit yes or no.

Common Belief: ROW_NUMBER assigns the same number to rows with the same values.

Reality: ROW_NUMBER always assigns unique sequential numbers, ignoring ties.

Why it matters: Assuming ROW_NUMBER handles ties like RANK leads to incorrect ranking and confusion in results.

Quick: Does RANK always assign consecutive numbers without gaps? Commit yes or no.

Common Belief: RANK assigns consecutive numbers without skipping any ranks.

Reality: RANK assigns the same rank to ties but skips numbers after the tie, creating gaps.

Why it matters: Misunderstanding this causes errors in interpreting rankings, especially in competitions or leaderboards.

Quick: Does DENSE_RANK skip numbers after ties? Commit yes or no.

Common Belief: DENSE_RANK skips numbers after ties just like RANK does.

Reality: DENSE_RANK does not skip numbers after ties; it assigns consecutive ranks.

Why it matters: Confusing DENSE_RANK with RANK can lead to wrong assumptions about ranking continuity.

Quick: Does PARTITION BY affect the global numbering of rows? Commit yes or no.

Common Belief: PARTITION BY does not restart numbering; it only filters rows.

Reality: PARTITION BY restarts numbering for each group, creating separate rankings per partition.

Why it matters: Ignoring partitioning effects leads to wrong rankings when grouping data.

Expert Zone

1

Ranking functions depend heavily on the ORDER BY clause; subtle changes there can drastically change results.

2

NULL values are treated as lowest by default in ORDER BY, which affects ranking positions unless explicitly handled.

3

Using multiple columns in ORDER BY can create complex tie scenarios that affect how ranks are assigned.

When NOT to use

Avoid ranking functions when working with extremely large datasets without proper indexing or when approximate rankings suffice; alternatives include pre-aggregated tables or approximate algorithms. Also, for simple row numbering without ordering, use simpler methods.

Production Patterns

Common patterns include paginating query results using ROW_NUMBER, generating leaderboards with RANK or DENSE_RANK, and grouping rankings by categories using PARTITION BY. These are often combined with filtering to select top N per group or to handle ties gracefully.

Connections

Window Functions

Ranking functions are a subset of window functions that operate over ordered partitions of data.

Understanding ranking functions opens the door to mastering window functions, which enable powerful analytics in SQL.

Sorting Algorithms

Ranking functions rely on sorting rows internally to assign positions correctly.

Knowing how sorting works helps optimize queries using ranking functions and anticipate performance impacts.

Sports Competition Scoring

Ranking functions mimic real-world scoring systems where ties and positions matter.

Recognizing this connection helps understand why different ranking methods exist and when to use each.

Common Pitfalls

#1 Assuming ROW_NUMBER assigns the same number to tied rows.

Wrong approach:

SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM players;

Correct approach:

Use RANK() or DENSE_RANK() instead to handle ties:
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM players;

Root cause:

Misunderstanding that ROW_NUMBER ignores ties and always gives unique numbers.

#2 Using RANK when you want consecutive ranks without gaps.

Wrong approach:

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM players;

Correct approach:

Use DENSE_RANK() to avoid gaps:
SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank FROM players;

Root cause:

Not knowing the difference between RANK and DENSE_RANK in handling gaps.

#3 Forgetting to use PARTITION BY when ranking within groups.

Wrong approach:

SELECT category, name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM players;

Correct approach:

Add PARTITION BY to rank within each category:
SELECT category, name, score, RANK() OVER (PARTITION BY category ORDER BY score DESC) AS rank FROM players;

Root cause:

Not realizing that ranking functions operate globally unless partitioned.

Key Takeaways

ROW_NUMBER, RANK, and DENSE_RANK assign numbers to rows based on order but differ in handling ties.

ROW_NUMBER always gives unique numbers, RANK assigns the same rank to ties and skips numbers after, and DENSE_RANK assigns the same rank to ties without gaps.

Using PARTITION BY restarts numbering within groups, enabling grouped rankings.

Choosing the right ranking function depends on whether you want unique positions or tied ranks with or without gaps.

Understanding these functions deeply helps write clearer, more accurate SQL queries for ranking and pagination.