SQL Help

Get the latest row per group in MySQL

Updated August 2026 · First published 2007 by David J. Lake

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:

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 the ORDER BY.
• Ties are explicit. ROW_NUMBER() picks exactly one row even when years tie (add a tiebreaker column to the ORDER BY to control which). If you'd rather keep all tied rows, use RANK() 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.