MySQL: get the latest row per group with ROW_NUMBER()

To get the latest row per group in MySQL 8.0 or later, number each group's rows with ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC) and keep row 1. End that ORDER BY with a unique column such as the primary key, or two rows with the same timestamp decide the winner by chance.

Here is each customer's most recent order, one row per customer:

WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM orders o
)
SELECT id, customer_id, created_at, total
FROM ranked
WHERE rn = 1
ORDER BY customer_id;

PARTITION BY restarts the numbering for each customer, ORDER BY created_at DESC puts the newest order first, and rn = 1 keeps only that one. The id DESC at the end is not decoration; it is the fix for the trap further down.

Window functions and common table expressions arrived together in MySQL 8.0. MySQL 8.0 itself reached end of life in April 2026, so on a supported server (8.4 LTS or 9.7 LTS) this is available everywhere.

The sample data

Every result below comes from this table. Note that customer 20 has two orders with the same timestamp: an import, a retry, two items checked out in the same second.

CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT NOT NULL,
  created_at  DATETIME NOT NULL,
  total       DECIMAL(10,2) NOT NULL
);

INSERT INTO orders VALUES
 (1, 10, '2026-09-01 09:00:00', 25.00),
 (2, 10, '2026-09-14 16:30:00', 40.00),
 (3, 20, '2026-09-03 11:15:00', 12.50),
 (4, 20, '2026-09-20 08:05:00', 99.00),
 (5, 20, '2026-09-20 08:05:00', 15.00),
 (6, 30, '2026-08-28 19:45:00', 60.00);

The queries here were run against SQLite 3.53, which implements the same standard window-function syntax, to check the logic and the outputs. MySQL-specific behavior is cited from the MySQL reference manual.

What it replaces

Before 8.0, the usual answer was one of two patterns. Both still work, and both have the same flaw.

The correlated subquery

SELECT o.id, o.customer_id, o.created_at, o.total
FROM orders o
WHERE o.created_at = (
  SELECT MAX(o2.created_at)
  FROM orders o2
  WHERE o2.customer_id = o.customer_id
);

The self-join anti-join

Join each order to any later order for the same customer, and keep the ones where no later order exists:

SELECT o1.id, o1.customer_id, o1.created_at, o1.total
FROM orders o1
LEFT JOIN orders o2
  ON o2.customer_id = o1.customer_id
 AND o2.created_at > o1.created_at
WHERE o2.id IS NULL;

Both return the same thing:

id | customer_id | created_at          | total
2  | 10          | 2026-09-14 16:30:00 | 40.00
4  | 20          | 2026-09-20 08:05:00 | 99.00
5  | 20          | 2026-09-20 08:05:00 | 15.00
6  | 30          | 2026-08-28 19:45:00 | 60.00

Four rows for three customers. Each pattern asks "which rows have the maximum timestamp", and two rows do. The result is only "one per group" as long as timestamps never collide, and nothing enforces that.

The damage shows up one step later. Sum the "latest order" totals from the self-join and you get 214.00; the correct figure, one order per customer, is 115.00. Customer 20's tied orders were both counted. (If what you need is the opposite, dropping a whole group when any of its rows matches, that is a different GROUP BY pattern.)

The GROUP BY that looks right and is not

A third pattern still circulates from the MySQL 5.x era:

SELECT customer_id, id, total, MAX(created_at)
FROM orders
GROUP BY customer_id;

On current MySQL this fails with error 1055, because ONLY_FULL_GROUP_BY is enabled by default. With that mode switched off it runs, and the manual is plain about the result: the server is "free to choose any value from each group". MAX(created_at) is the newest date, but id and total can come from any row in the group. If you inherit this query on a server with a relaxed sql_mode, it has been returning mismatched rows without any error.

Why rn cannot go in the WHERE clause

The obvious shortcut fails:

SELECT id, customer_id,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders
WHERE rn = 1;   -- rejected

MySQL's manual gives the reason: window functions are permitted only in the select list and ORDER BY, and windowing runs after WHERE, GROUP BY and HAVING. When WHERE is evaluated, rn does not exist yet. SQLite refuses it the same way (misuse of aliased window function rn). Wrapping the query in a CTE or derived table, as above, is the standard way to filter on it.

The index

Give the window an index that matches its partition and order:

CREATE INDEX idx_orders_customer_latest
  ON orders (customer_id, created_at DESC, id DESC);

Current MySQL stores DESC index columns in descending order rather than ignoring the keyword, and the optimizer can use an index whose columns mix ascending and descending order. Run EXPLAIN on your own data to confirm it is chosen (the MySQL cheat sheet covers reading it); whether the window still sorts depends on the plan.

When a lateral join can be faster

The window version reads every order for every customer, then discards all but one per group. With millions of orders and a few thousand customers, most of that reading is wasted. From MySQL 8.0.14, a LATERAL derived table can fetch just the newest row per customer, driven from a customers table, through the same index:

SELECT c.id AS customer_id, latest.id, latest.created_at, latest.total
FROM customers c,
LATERAL (
  SELECT o.id, o.created_at, o.total
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC, o.id DESC
  LIMIT 1
) AS latest;

That can be one short index lookup per customer instead of a full pass over orders, and a customer with no orders drops out, as with an inner join. The tie-breaker rule applies here too. For most tables the ROW_NUMBER() version is fast enough and easier to read; switch to the lateral join when the number of rows per group is large and EXPLAIN ANALYZE shows the window scan dominating.