intermediate

ROW_NUMBER

By DataPlayArena Technical Review BoardUpdated 2026-07-236 min readRuntime Verified

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

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

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:

sql
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

Challenge 1: Rank Employees by Salary

Select 'name', 'salary', and ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn from 'employees'.


Summary

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

DPA

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.