ROW_NUMBER() gives each row a unique sequential number (1, 2, 3, …) inside its partition, following the ORDER BY of the window.
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(50) NOT NULL
);
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
ordered_on DATE NOT NULL,
total DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL
);
INSERT INTO customers (name, city) VALUES
('Maya Shah', 'Pune'),
('Rohan Verma', 'Delhi'),
('Sana Ali', 'Pune'),
('Tara Nair', 'Kochi'),
('Uday Singh', 'Delhi');
INSERT INTO orders (customer_id, ordered_on, total, status) VALUES
(1, '2026-05-02', 680.00, 'delivered'),
(1, '2026-05-20', 1299.00, 'delivered'),
(2, '2026-05-21', 145.00, 'shipped'),
(3, '2026-06-01', 450.00, 'delivered'),
(3, '2026-06-05', 80.00, 'cancelled'),
(3, '2026-06-18', 999.00, 'shipped'),
(2, '2026-06-20', 220.00, 'pending');SELECT customer_id, id AS order_id, ordered_on, total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_on, id) AS order_number
FROM orders
ORDER BY customer_id, order_number;+-------------+----------+------------+---------+--------------+
| customer_id | order_id | ordered_on | total | order_number |
+-------------+----------+------------+---------+--------------+
| 1 | 1 | 2026-05-02 | 680.00 | 1 |
| 1 | 2 | 2026-05-20 | 1299.00 | 2 |
| 2 | 3 | 2026-05-21 | 145.00 | 1 |
| 2 | 7 | 2026-06-20 | 220.00 | 2 |
| 3 | 4 | 2026-06-01 | 450.00 | 1 |
| 3 | 5 | 2026-06-05 | 80.00 | 2 |
| 3 | 6 | 2026-06-18 | 999.00 | 3 |
+-------------+----------+------------+---------+--------------+
7 rows in setEach customer’s orders are numbered in date order, and the count restarts for each customer. Customer 3’s third order has order_number = 3.
The latest (or first) row per group
Number the rows, then keep number 1. Use DESC order for “latest”:
SELECT customer_id, order_id, ordered_on, total
FROM (
SELECT customer_id, id AS order_id, ordered_on, total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_on DESC, id DESC) AS rn
FROM orders
) AS numbered
WHERE rn = 1
ORDER BY customer_id;+-------------+----------+------------+---------+
| customer_id | order_id | ordered_on | total |
+-------------+----------+------------+---------+
| 1 | 2 | 2026-05-20 | 1299.00 |
| 2 | 7 | 2026-06-20 | 220.00 |
| 3 | 6 | 2026-06-18 | 999.00 |
+-------------+----------+------------+---------+
3 rows in setThe most recent order of each customer, exactly one per customer. This is the standard pattern for latest-row-per-group (Top N per Group).
Ties get different numbers
ROW_NUMBER never repeats a number. If two rows have the same value in the ORDER BY, they still get different numbers, and which one gets the lower number is not defined unless you add a tie-breaker:
SELECT id, total, ROW_NUMBER() OVER (ORDER BY total DESC, id) AS position FROM orders ORDER BY position;+----+---------+----------+
| id | total | position |
+----+---------+----------+
| 2 | 1299.00 | 1 |
| 6 | 999.00 | 2 |
| 1 | 680.00 | 3 |
| 4 | 450.00 | 4 |
| 7 | 220.00 | 5 |
| 3 | 145.00 | 6 |
| 5 | 80.00 | 7 |
+----+---------+----------+
7 rows in setid makes the order deterministic. Always end the window’s ORDER BY with a unique column.
Remove duplicates
Keep one row for each email, deleting the others (DELETE with JOIN):
CREATE TABLE subscribers (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(30), subscribed_on DATE);
INSERT INTO subscribers (email, subscribed_on) VALUES
('a@example.com', '2026-01-01'), ('b@example.com', '2026-01-05'), ('a@example.com', '2026-02-01'), ('a@example.com', '2026-03-01');
SELECT id, email, subscribed_on, ROW_NUMBER() OVER (PARTITION BY email ORDER BY subscribed_on) AS copy_number
FROM subscribers
ORDER BY email, copy_number;+----+---------------+---------------+-------------+
| id | email | subscribed_on | copy_number |
+----+---------------+---------------+-------------+
| 1 | a@example.com | 2026-01-01 | 1 |
| 3 | a@example.com | 2026-02-01 | 2 |
| 4 | a@example.com | 2026-03-01 | 3 |
| 2 | b@example.com | 2026-01-05 | 1 |
+----+---------------+---------------+-------------+
4 rows in setRows with copy_number > 1 are the duplicates. Check them with a SELECT, then delete them in a statement such as DELETE ... WHERE id IN (SELECT id FROM (...) t WHERE copy_number > 1).
A sequence number in a report
SELECT ROW_NUMBER() OVER (ORDER BY total DESC, id) AS `#`, id AS order_id, total FROM orders ORDER BY `#` LIMIT 3;+---+----------+---------+
| # | order_id | total |
+---+----------+---------+
| 1 | 2 | 1299.00 |
| 2 | 6 | 999.00 |
| 3 | 1 | 680.00 |
+---+----------+---------+
3 rows in set(# needs backticks as an alias. A plain name such as position is simpler.)
Pagination with row numbers
LIMIT ... OFFSET is simpler for ordinary pagination (Pagination). Row numbers are useful when each group needs its own page, or when you need the number itself:
SELECT customer_id, order_id, total
FROM (
SELECT customer_id, id AS order_id, total, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC, id) AS rn FROM orders
) AS t
WHERE rn BETWEEN 1 AND 2
ORDER BY customer_id, rn;+-------------+----------+---------+
| customer_id | order_id | total |
+-------------+----------+---------+
| 1 | 2 | 1299.00 |
| 1 | 1 | 680.00 |
| 2 | 7 | 220.00 |
| 2 | 3 | 145.00 |
| 3 | 6 | 999.00 |
| 3 | 4 | 450.00 |
+-------------+----------+---------+
6 rows in setEach customer’s two largest orders.
Every n-th row
SELECT id, total FROM (SELECT id, total, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM orders) AS t WHERE MOD(rn, 3) = 0 ORDER BY id;+----+--------+
| id | total |
+----+--------+
| 3 | 145.00 |
| 6 | 999.00 |
+----+--------+
2 rows in setCompared with the old way
Before MySQL 8, people used a user variable: SELECT @n := @n + 1 .... It was fragile (the evaluation order was not guaranteed) and is deprecated. ROW_NUMBER() is the right tool (User Variables).
Row number is not rank
ROW_NUMBER() always gives distinct numbers. If tied rows must share a position, use RANK() or DENSE_RANK() (RANK).