sqlindex

Window functions

Top-N rows per group in SQL

3 min · updated June 16, 2026

“Top 3 orders per customer” is ROW_NUMBER() partitioned by the group, then filtered. The only difference between dialects is where you can filter the window result.

-- MySQL 8+, PostgreSQL, SQLite 3.25+  (no QUALIFY — wrap in a subquery/CTE)
SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
) t
WHERE rn <= 3;

-- BigQuery, Snowflake, DuckDB  (QUALIFY filters the window inline — no subquery)
SELECT *
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) <= 3;

Just the single best row per group (N = 1)? PostgreSQL has a shortcut:

-- PostgreSQL: DISTINCT ON keeps the first row per group in the ORDER BY
SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, amount DESC;

ROW_NUMBER vs RANK for ties. ROW_NUMBER returns exactly N rows even when amounts tie (the tie is broken arbitrarily — add a tiebreaker like ORDER BY amount DESC, id). If you want to keep all ties, use RANK() instead, which can return more than N rows. See the ranking recipe.

Works in MySQL 8+, PostgreSQL, SQLite 3.25+, BigQuery, Snowflake and DuckDB.

← All recipes