Fetching the Latest Record per Name Group in MySQL 5.6/5.7

A colleague needed to retrieve, for each name group, the row with the most recent create_date from a MySQL 5.6 table. The initial attempt used:

select name, value, create_date, update_date from t1 group by name order by create_date desc;

This returned the first inserted row for each group, not the latest, because MySQL 5.6 allows non‑aggregated columns in the SELECT list without strict GROUP BY checking.

By applying a “divide‑and‑conquer” approach—sorting first, then grouping—the correct result can be obtained:

select * from (select name, value, create_date, update_date from t1 order by create_date desc) t group by t.name;

The article also notes that MySQL 5.7 (and later) enable the ONLY_FULL_GROUP_BY mode by default, which enforces that selected columns must be aggregated or appear in the GROUP BY clause, causing the previous query to fail with an error.

To achieve the same outcome without relying on the lax 5.6 behavior, you can either remove ONLY_FULL_GROUP_BY from sql_mode or rewrite the query, for example:

select * from t1 a where create_date = (select max(create_date) from t1 b where a.name = b.name);

or

select * from t1 a where not exists (select * from t1 b where a.name = b.name and b.create_date > a.create_date);

MySQL 8.0 introduces window functions, allowing a more straightforward solution similar to Oracle:

select * from (select name, create_date, row_number() over (partition by name order by create_date desc) as r from t1) where r = 1;

Oracle can achieve the same result with its own row_number() window function:

select * from (select name, create_date, row_number() over (partition by name order by create_date desc) as r from t1) where r = 1;

The discussion highlights the importance of understanding SQL mode differences when migrating from MySQL 5.6 to 5.7 and offers multiple viable query patterns for retrieving the latest record per group.