ROW_NUMBER

Overview

ROW_NUMBER() assigns a unique, sequential integer to each row within a partition, starting at 1 for the first row of each partition.

Learning Objectives

• Assign unique row indices using ROW_NUMBER() OVER (...).
• Deduplicate records by ranking rows and filtering WHERE row_num = 1.
• Partition window calculations using PARTITION BY.

Detailed Concept Explanation

Unlike standard aggregate functions (which collapse multiple rows into a single summary row), Window Functions compute values across a set of rows while keeping every individual row intact in the output.

ROW_NUMBER() assigns a strictly sequential index (1, 2, 3, 4...) based on the ORDER BY specification inside the OVER() clause.

Code Examples

SQL

-- Assign sequential row numbers per department sorted by salary
SELECT 
    name,
    department_id,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS row_num
FROM employees;

-- Deduplication Pattern: Keep only the latest order per customer
WITH RankedOrders AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY order_date DESC
        ) AS rn
    FROM orders
)
SELECT * FROM RankedOrders WHERE rn = 1;

Best Practices

• Always Include ORDER BY: ROW_NUMBER() requires an explicit ORDER BY inside OVER() so the sequence assignment is deterministic.
• Top N per Group: Use ROW_NUMBER() inside a CTE or subquery, then filter WHERE rn <= N to get the Top N records per category.

Interview Perspective

WITH Ranked AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY category ORDER BY score DESC) rn FROM data
)
SELECT * FROM Ranked WHERE rn <= N;

Interactive Challenges

Summary

ROW_NUMBER() assigns unique, non-overlapping sequential numbers to rows within partitions, making it ideal for deduplication and Top N queries.