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 explicitORDER BYinsideOVER()so the sequence assignment is deterministic. - Top N per Group: Use
ROW_NUMBER()inside a CTE or subquery, then filterWHERE rn <= Nto get the Top N records per category.
Interview Perspective
Note
Interview Question: How do you find the Top N records per category in SQL?
Use ROW_NUMBER() partitioned by the category column and ordered by the ranking metric, wrapped inside a CTE:
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.
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.