Practical SQL Guide · MySQL 8.0

Top 1 per group: why a global LIMIT is not enough

Implement Top 1 or Top N per group with window functions and explicit rules for ties and row counts.

Problem and reasoning

ORDER BY ... LIMIT 1 returns one row from the entire result set; it cannot answer “the highest-value order in every department.” Top N per group first partitions rows, orders and numbers each partition independently, and then filters the required ranks.

MySQL 8 can assign ROW_NUMBER() OVER (PARTITION BY department ORDER BY amount DESC, id). PARTITION BY defines groups, the window ORDER BY defines priority inside each group, and the unique id breaks equal-amount ties deterministically.

ROW_NUMBER gives each number to exactly one row and fits “exactly one winner per group.” If all tied winners must remain, use RANK or DENSE_RANK and filter rank 1. The business rule must decide whether ties are broken or preserved.

A window alias cannot be filtered in the same query block's WHERE clause, so put the ranking in a CTE or subquery and apply WHERE rn <= N outside. For N greater than one, keep deterministic ordering and verify the returned count per group.

At larger scale, evaluate a composite index aligned with the partition and order columns, but window evaluation may still read and sort many candidates. Reduce input with valid WHERE predicates first and inspect the real plan with EXPLAIN ANALYZE.

Examples

Sample table

Table：orders

orders
name	department	amount
Alice	Sales	1200
Bob	IT	900
Cindy	Sales	1500
David	HR	700

Before / Not recommended

SELECT name, department, amount FROM orders ORDER BY amount DESC LIMIT 1;

Before result

result
name	department	amount
Cindy	Sales	1500

After / Recommended

WITH ranked AS (SELECT id, name, department, amount, ROW_NUMBER() OVER (PARTITION BY department ORDER BY amount DESC, id) AS rn FROM orders) SELECT name, department, amount FROM ranked WHERE rn = 1 ORDER BY department;

After result

result
name	department	amount
David	HR	700
Bob	IT	900
Cindy	Sales	1500

Why it is better：Global LIMIT returns only the single highest row overall. Partitioned ranking restarts numbering per department, producing a winner for HR, IT, and Sales.