Get the latest row per group in MySQL
Based on the SQL Help series David J. Lake published here in 2006 to 2009. Rewritten for MySQL 8 by the WeFixTech editorial team.
This is probably the most-asked SQL question of all time, in a hundred disguises: the newest post per user, the latest price per product, the last login per account, the most recent model year per car. Database people call it greatest-n-per-group. Once you can spot the shape, you can solve every variant the same way.
Sample data: an inventory of car models with their release years:
CREATE TABLE cars (
make VARCHAR(20),
model VARCHAR(20),
year SMALLINT
);
INSERT INTO cars VALUES
('Ford','Focus',2022), ('Ford','Focus',2021), ('Ford','Focus',2018),
('Kia','Sportage',2025), ('Kia','Sportage',2023), ('Kia','Sportage',2021),
('Skoda','Octavia',2024), ('Skoda','Octavia',2020);
We want one row per make and model: the newest:
| make | model | year |
|---|---|---|
| Ford | Focus | 2022 |
| Kia | Sportage | 2025 |
| Skoda | Octavia | 2024 |
The modern way: ROW_NUMBER()
Since MySQL 8.0, window functions make this direct. Number the rows inside each group, newest first, then keep row 1 of every group:
SELECT make, model, year
FROM (
SELECT make, model, year,
ROW_NUMBER() OVER (
PARTITION BY make, model
ORDER BY year DESC
) AS rn
FROM cars
) AS ranked
WHERE rn = 1;
PARTITION BY defines the groups (one per make+model),
ORDER BY year DESC decides who's first inside each group, and the outer
WHERE rn = 1 keeps the winners. Two things make this the go-to version:
- It extends naturally. Latest three per group?
WHERE rn <= 3. Oldest instead of newest? Flip theORDER BY. - Ties are explicit.
ROW_NUMBER()picks exactly one row even when years tie (add a tiebreaker column to theORDER BYto control which). If you'd rather keep all tied rows, useRANK()instead.
The classic way: a correlated subquery
Before window functions, the standard answer was a subquery that runs once per candidate row, asking "is your year the maximum for your group?":
SELECT c1.make, c1.model, c1.year
FROM cars AS c1
WHERE c1.year = (
SELECT MAX(c2.year)
FROM cars AS c2
WHERE c2.make = c1.make
AND c2.model = c1.model
);
The pattern to internalise: the columns that define the group (make,
model) become matching conditions inside the subquery; the column you want the
maximum of (year) is what the outer WHERE compares. Note that
if two rows tie for the max, this version returns both.
Another pre-8.0 favourite is the self-join: join each row to any row in the same group with a greater year, and keep rows that found none:
SELECT c1.make, c1.model, c1.year FROM cars AS c1 LEFT JOIN cars AS c2 ON c2.make = c1.make AND c2.model = c1.model AND c2.year > c1.year WHERE c2.year IS NULL;
Real tables: think about indexes
On a big table, all of these want a composite index matching the group columns
plus the ordering column: here
INDEX (make, model, year). With it, MySQL can find each group's top row
without scanning everything; without it, every version of this query degrades to a
full scan and sort.