Window Functions - Difference between ROW_NUMBER(), RANK(), DENSE_RANK()

This lesson on Window Functions - Difference between ROW_NUMBER(), RANK(), DENSE_RANK() is hands-on and example-driven. You will be able to apply SQL window functions to rank, segment, and filter records without collapsing individual table rows. You will master the exact tie-breaking differences between ROW_NUMBER(), RANK(), and DENSE_RANK(), and use subqueries with PARTITION BY to extract top-N items per group.

What You'll Be Able To Do

• Construct window specifications using the OVER clause with ORDER BY to rank ordered datasets.
• Select between ROW_NUMBER(), RANK(), and DENSE_RANK() based on specific business rules for tie handling.
• Partition result sets across categorical dimensions using PARTITION BY to compute isolated group-level ranks.
• Implement nested subqueries to filter rows by window function rank values.

Detailed Concept Walkthrough

1. Window Architecture and the OVER Clause

Window functions perform row-level calculations across a specified subset or view of data without collapsing rows like standard GROUP BY aggregations. The OVER clause defines the boundaries, sorting, and scope of that calculation window.

• Mechanism: The OVER clause instructs the SQL engine to establish a window frame across the dataset. Every individual row retains its identity in the final output while gaining access to calculation results derived across the entire window.
• Execution Flow: The database sorts the rows within the window frame according to the ORDER BY specification inside OVER before calculating ranking indices.
• Best Practice: Always specify an explicit sort order inside the OVER clause when computing ranking functions, as ranking calculations inherently depend on deterministic ordering.

-- Compute row-by-row popularity rank across all baby names
SELECT
  name,
  gender,
  total,
  ROW_NUMBER() OVER (ORDER BY total DESC) AS popularity_rank
FROM baby_names;

Key Takeaway: The OVER clause creates an analytical window across records while preserving every individual row in the output.

2. Evaluating Tie Behaviors in Ranking Functions

SQL provides three distinct ranking functions whose differences emerge entirely when two or more records evaluate to identical values in the ORDER BY column.

• Mechanism: ROW_NUMBER() enforces strict uniqueness by assigning incremental integers (1, 2, 3...) regardless of identical values. RANK() assigns identical rank numbers to ties and skips subsequent ranks to reflect positional count. DENSE_RANK() assigns identical ranks to ties without skipping subsequent numbers.
• Under the Hood: When ties occur (e.g., Isabella and Olivia both having 5,000 count), RANK() tracks total rows evaluated and jumps numerical rank, whereas DENSE_RANK() only increments rank when distinct values change.
• Syntax Rule: None of the three ranking functions take column arguments inside their parentheses; ordering and partitioning criteria reside exclusively inside the OVER clause.

-- Side-by-side comparison of tie-handling behaviors
SELECT
  name,
  total,
  ROW_NUMBER() OVER (ORDER BY total DESC) AS row_num,
  RANK() OVER (ORDER BY total DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY total DESC) AS dense_rnk
FROM baby_names
WHERE total >= 5000;

Key Takeaway: Choose ROW_NUMBER for arbitrary unique ordering, RANK when gap counts matter, and DENSE_RANK for continuous tier numbering.

3. Data Segmentation with PARTITION BY

The PARTITION BY clause divides the dataset into independent subsets, causing window calculations to reset at the boundary of each partition.

• Mechanism: Adding PARTITION BY column_name inside the OVER clause splits calculation boundaries into distinct partitions. The window function calculates ranks starting from 1 for each isolated group.
• Execution Flow: The SQL engine groups rows sharing the partition key together, sorts each partition internally based on the local ORDER BY clause, and applies the ranking algorithm independently to each group.
• Best Practice: Combine PARTITION BY with multiple ranking functions to evaluate intra-group standings without writing multiple isolated queries or join operations.

-- Rank baby names separately within each gender category
SELECT
  name,
  gender,
  total,
  DENSE_RANK() OVER (
    PARTITION BY gender 
    ORDER BY total DESC
  ) AS gender_rank
FROM baby_names;

Key Takeaway: PARTITION BY isolates window calculations to subgroups and resets rank counters across category boundaries.

4. Filtering Window Results via Subqueries

SQL logical execution order prevents referencing window function aliases directly inside the WHERE clause, requiring subqueries or common table expressions to filter rank outputs.

• Execution Flow: The WHERE clause evaluates before window functions in SQL processing order. Attempting to filter window outputs directly in the same SELECT block causes an immediate syntax error.
• Mechanism: Wrapping the window query inside an inner subquery allows the outer query's WHERE clause to filter directly on the derived rank column.
• Best Practice: Name the computed rank column clearly in the inner query to simplify filtering conditions such as top-N thresholds in the outer query.

-- Extract top 3 most popular names per gender using a subquery
SELECT
  name,
  gender,
  total,
  gender_rank
FROM (
  SELECT
    name,
    gender,
    total,
    ROW_NUMBER() OVER (
      PARTITION BY gender 
      ORDER BY total DESC
    ) AS gender_rank
  FROM baby_names
) AS ranked_names
WHERE gender_rank <= 3;

Key Takeaway: Wrap window queries in subqueries to filter by calculated ranks in the outer WHERE clause.

Topics Covered in Window Functions - Difference between ROW_NUMBER(), RANK(), DENSE_RANK()

1. Foundations: ORDER BY (0:28 - 1:18) — The instructor demonstrates sorting the baby_names table by total popularity in descending order.
2. Window Functions and OVER (1:18 - 2:36) — The instructor introduces the OVER clause alongside ROW_NUMBER to calculate sequential ranks without row aggregation.
3. Comparing Ranking Functions (3:28 - 5:13) — The instructor compares ROW_NUMBER, RANK, and DENSE_RANK using a tied count example between Isabella and Olivia.
4. Data Segmentation with PARTITION BY (5:13 - 6:52) — The instructor demonstrates dividing the dataset into independent subgroups by partitioning calculations across gender categories.
5. Window Functions in Subqueries (6:52 - 7:52) — The instructor wraps window calculations inside a subquery to filter and isolate the top three names per gender.

SQL Cheat Sheet

• ROW_NUMBER() OVER (ORDER BY col [ASC|DESC]) — Assigns consecutive unique integers to ordered rows

  SELECT name, ROW_NUMBER() OVER (ORDER BY total DESC) FROM baby_names;
  

• RANK() OVER (ORDER BY col [ASC|DESC]) — Ranks rows with ties, skipping subsequent ranks

  SELECT name, RANK() OVER (ORDER BY total DESC) FROM baby_names;
  

• DENSE_RANK() OVER (ORDER BY col [ASC|DESC]) — Ranks rows with ties, without skipping ranks

  SELECT name, DENSE_RANK() OVER (ORDER BY total DESC) FROM baby_names;
  

• OVER (PARTITION BY col ORDER BY col) — Calculates window functions independently within defined sub-groups

  SELECT name, RANK() OVER (PARTITION BY gender ORDER BY total DESC) FROM baby_names;
  

• Subquery Filter on Window Rank — Filters ranking output using outer WHERE condition

  SELECT * FROM (SELECT name, RANK() OVER (ORDER BY total DESC) AS r FROM baby_names) t WHERE r <= 3;
  

Comparison Table

Common Pitfalls

• Mistake: Attempting to filter window function outputs directly in the WHERE clause of the same SELECT query. Avoid: Encapsulate the window calculation in a subquery and apply the WHERE filter in the outer query.
• Mistake: Passing a column name inside the parentheses of ROW_NUMBER(), RANK(), or DENSE_RANK(). Avoid: Keep parentheses empty and specify all sorting columns inside the OVER clause.
• Mistake: Using RANK() when business requirements demand consecutive ranking numbers after tied records. Avoid: Use DENSE_RANK() to prevent skipped rank values when duplicate metrics occur.

FAQs

• Why can't I filter a window function directly in a WHERE clause? SQL evaluates WHERE clauses before window functions in the logical query lifecycle. To filter by calculated rank, wrap the query in a subquery and filter in the outer statement.
• How does ROW_NUMBER decide which tied row gets the lower number? Unless secondary sort columns are specified in the ORDER BY clause, ROW_NUMBER resolves ties non-deterministically based on the engine's physical row retrieval order.
• Does PARTITION BY reduce or collapse rows like GROUP BY? No. PARTITION BY defines independent computational subsets for the window calculation while preserving every original row in the output.