ROW_NUMBER() OVER

Data & Storage

Meaning

Assigns a unique sequential integer to each row within a partition of a result set, ordered by specified columns. Used to rank or number rows within groups.

Primary Function

Window function / ranking

Communicative Purpose

Provides row numbers within groups for ordering, deduplication, pagination, or analytic calculations.

Pattern

ROW_NUMBER() OVER (PARTITION BY partition_col ORDER BY order_col)

Core Structure

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

Função primária

Window function / ranking

Propósito comunicativo

Provides row numbers within groups for ordering, deduplication, pagination, or analytic calculations.

Situações de gatilho

Generating row numbers for pagination; identifying duplicates within groups; ranking sales per region; preparing data for top-N queries.

Contextos

Relational databases (SQL Server, PostgreSQL, MySQL 8+, Oracle, SQLite) and data warehouses; SQL queries, ETL pipelines, reporting.

Padrão

ROW_NUMBER() OVER (PARTITION BY partition_col ORDER BY order_col)

Estrutura central

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

Slots de substituição

partition_col: column name or expression; order_col: column name or expression (can be multiple separated by commas).

Colocados típicos

WHERE
ORDER BY
GROUP BY
CTE
subqueries
FILTER
QUALIFY.

Substituições comuns

RANK()
DENSE_RANK()
NTILE(n) for different ranking semantics.

Erros comuns

Forgetting to include ORDER BY leading to nondeterministic row numbers; using ROW_NUMBER() without PARTITION BY when intending to number entire result set; confusing with RANK() when ties matter.

Similar / contraste

RANK() – same syntax but leaves gaps when ties; DENSE_RANK() – no gaps; NTILE(n) – distributes rows into n buckets.

Interferências

Coming from procedural languages: may expect row numbers to reflect physical order; in SQL order is not guaranteed without ORDER BY.

Família do chunk

Window functions
ranking functions
analytic functions
OVER clause

Nuance

ROW_NUMBER() is nondeterministic if ORDER BY does not uniquely identify rows; performance can be impacted by lack of indexes on partition/order columns; some databases allow FRAME clauses but not needed for simple ROW_NUMBER().

Efeito pragmático

Enables efficient top-N queries and duplicate detection without self-joins.

Dica de memória

Think 'row number over partition order' – like giving each row a seat number within its group.

Nota

If the ORDER BY clause does not uniquely order rows, the generated numbers are nondeterministic; ensure deterministic ordering or add tie‑breaker columns for reproducible results

Upgrade path

Use QUALIFY ROW_NUMBER() = 1 to deduplicate, or combine with window frames for running totals.

Frequência: HighFormulaicidade: Semi-fixedTipo de construção: code_patternPrioridade de aquisição: Active recallPrioridade de output: BothTag de espaçamento: Medium-term

Examples

SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM orders WHERE rn BETWEEN 21 AND 40;

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn FROM users ) AS ranked WHERE rn = 1;

SELECT department, employee_name, salary FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees ) AS dept_ranked WHERE rn <= 3;