Window Functions · Advanced

ROW_NUMBER

Assigns a continuous number to rows after ordering or partitioning.

This topic uses window functions or CTEs; MySQL requires version 8.0 or later.

Overview

ROW_NUMBER assigns a continuous unique number according to the ORDER BY inside OVER without collapsing rows, and PARTITION BY restarts numbering per group. The window ORDER BY controls numbering only; use a query-level ORDER BY when the displayed row order must also be guaranteed.

Basic syntax

SELECT name, ROW_NUMBER() OVER (ORDER BY amount DESC, id) AS rn FROM orders ORDER BY amount DESC, id;

Examples

ROW_NUMBER example

Number orders by amount descending, use id as a deterministic tie-breaker, and repeat the ordering at query level for stable display.

Sample table

Table：orders

orders
id	name	amount
1	Alice	1200
2	Bob	900
3	Cindy	1500
4	David	700

Run result

result
name	rn
Cindy	1
Alice	2
Bob	3
David	4

Common issues

How is ROW_NUMBER different from RANK?

ROW_NUMBER always assigns continuous numbers, while RANK leaves gaps after ties.

Does ROW_NUMBER need ORDER BY?

Usually yes inside the window; otherwise the numbering has no clear business meaning.

What is ROW_NUMBER used for?

Top-one-per-group, deduplication, pagination, and ordered row numbering.

Was this explanation clear?

RANK →