Top N per group in SQL: ROW_NUMBER, ties, LATERAL and no window

To get the top N rows per group in SQL, number the rows inside each group with ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) and keep the rows numbered N or less in an outer query. PARTITION BY restarts the numbering for every group, so each category gets its own top N. A plain LIMIT cannot do it, because LIMIT cuts the whole result, not each group.

How do I get the top N rows per group in SQL?

ROW_NUMBER() with PARTITION BY gets the top N rows per group. The window function numbers the products in each category from the highest revenue down, and the outer query keeps numbers 1 to 3. Here is the top 3 products in each category:

WITH products(category, product, revenue) AS (
  VALUES ('Books', 'Atlas', 900), ('Books', 'Novel', 1200), ('Books', 'Cookbook', 700),
         ('Books', 'Diary', 700), ('Games', 'Chess', 400), ('Games', 'Puzzle', 650),
         ('Games', 'Cards', 300), ('Games', 'Dice', 150), ('Toys', 'Kite', 500)
),
numbered AS (
  SELECT category, product, revenue,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC, product) AS rn
  FROM products
)
SELECT category, product, revenue
FROM numbered
WHERE rn <= 3
ORDER BY category, rn;

Number the rows with ROW_NUMBER() OVER (PARTITION BY group ORDER BY value DESC) and filter rn <= N in an outer query. The filter cannot go in the same query’s WHERE, because WHERE runs before window functions. Toys has one product, so it returns one row: a group with fewer than N rows returns all of them.

The same syntax works in PostgreSQL, MySQL 8.0 and SQL Server 2012 and later. This task is also called greatest-n-per-group.

Why does LIMIT return the top N rows overall instead of per group?

LIMIT 3 returns three rows for the whole query, after sorting, so the top N per group never appears. Every row in the result below is a book, because the three highest revenues all belong to Books:

WITH products(category, product, revenue) AS (
  VALUES ('Books', 'Atlas', 900), ('Books', 'Novel', 1200), ('Books', 'Cookbook', 700),
         ('Books', 'Diary', 700), ('Games', 'Chess', 400), ('Games', 'Puzzle', 650),
         ('Games', 'Cards', 300), ('Games', 'Dice', 150), ('Toys', 'Kite', 500)
)
SELECT category, product, revenue
FROM products
ORDER BY revenue DESC, product
LIMIT 3;

Adding GROUP BY category does not help either: a group then collapses to one row, and selecting product next to MAX(revenue) is an error in PostgreSQL. A per group limit needs a window function, a LATERAL join or a correlated subquery.

How do I keep ties in a top N per group query?

Use RANK() instead of ROW_NUMBER() when tied rows should all make the cut. Cookbook and Diary both earned 700 in Books. ROW_NUMBER() gives them 3 and 4 and drops Diary, while RANK() <= 3 gives both of them rank 3 and keeps them:

WITH products(category, product, revenue) AS (
  VALUES ('Books', 'Atlas', 900), ('Books', 'Novel', 1200), ('Books', 'Cookbook', 700),
         ('Books', 'Diary', 700), ('Games', 'Chess', 400), ('Games', 'Puzzle', 650),
         ('Games', 'Cards', 300), ('Games', 'Dice', 150), ('Toys', 'Kite', 500)
),
ranked AS (
  SELECT category, product, revenue,
         RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
  FROM products
)
SELECT category, product, revenue, rnk
FROM ranked
WHERE rnk <= 3
ORDER BY category, rnk, product;

Books now returns four rows. Ask which answer the question wants: exactly N rows per group is ROW_NUMBER(), everyone in the first N places is RANK(), and every row holding one of the N highest distinct values is DENSE_RANK(). The RANK vs DENSE_RANK vs ROW_NUMBER post shows all three on the same data.

Why does my top N per group query return different rows each time?

A top N per group query changes its answer when the window’s ORDER BY has ties and nothing breaks them. With ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) alone, Cookbook and Diary can swap places between runs, because the database may number equal rows in any order. Add a unique column after the sort column, as the first query does with product, and the result is the same on every run.

How do I get the top N rows per group with LATERAL?

CROSS JOIN LATERAL runs a small ORDER BY ... LIMIT query once for each group, so the limit applies per group. The outer query lists the categories, and the subquery after LATERAL can refer to the current category:

WITH products(category, product, revenue) AS (
  VALUES ('Books', 'Atlas', 900), ('Books', 'Novel', 1200), ('Books', 'Cookbook', 700),
         ('Books', 'Diary', 700), ('Games', 'Chess', 400), ('Games', 'Puzzle', 650),
         ('Games', 'Cards', 300), ('Games', 'Dice', 150), ('Toys', 'Kite', 500)
),
categories AS (
  SELECT DISTINCT category FROM products
)
SELECT c.category, t.product, t.revenue
FROM categories AS c
CROSS JOIN LATERAL (
  SELECT p.product, p.revenue
  FROM products AS p
  WHERE p.category = c.category
  ORDER BY p.revenue DESC, p.product
  LIMIT 3
) AS t
ORDER BY c.category, t.revenue DESC, t.product;

On a large table with few groups, LATERAL with an index on (category, revenue DESC) can read only the top rows of each group, where the window function numbers every row. In real schemas the categories usually come from their own table, which also keeps a category with no products out of the join. SQL Server spells the same idea CROSS APPLY with TOP (3).

How do I get the top 1 row per group in PostgreSQL?

DISTINCT ON (category) keeps the first row of each category in the query’s ORDER BY, which is the top 1 row per group in one short query. The ORDER BY must start with the DISTINCT ON columns, then sort the best row first:

WITH products(category, product, revenue) AS (
  VALUES ('Books', 'Atlas', 900), ('Books', 'Novel', 1200), ('Books', 'Cookbook', 700),
         ('Books', 'Diary', 700), ('Games', 'Chess', 400), ('Games', 'Puzzle', 650),
         ('Games', 'Cards', 300), ('Games', 'Dice', 150), ('Toys', 'Kite', 500)
)
SELECT DISTINCT ON (category) category, product, revenue
FROM products
ORDER BY category, revenue DESC, product;

DISTINCT ON is PostgreSQL only and returns exactly one row per group, so it cannot do a top 3. Elsewhere, use ROW_NUMBER() with rn = 1. Second highest salary in each department uses the same ranking with rnk = 2.

How do I get the top N per group without window functions?

Without window functions, keep a row when fewer than N rows in its group beat it. A correlated subquery counts the products in the same category with a higher revenue, or the same revenue and an earlier name, and the outer WHERE keeps the rows with a count below 3:

WITH products(category, product, revenue) AS (
  VALUES ('Books', 'Atlas', 900), ('Books', 'Novel', 1200), ('Books', 'Cookbook', 700),
         ('Books', 'Diary', 700), ('Games', 'Chess', 400), ('Games', 'Puzzle', 650),
         ('Games', 'Cards', 300), ('Games', 'Dice', 150), ('Toys', 'Kite', 500)
)
SELECT p.category, p.product, p.revenue
FROM products AS p
WHERE (
  SELECT COUNT(*)
  FROM products AS q
  WHERE q.category = p.category
    AND (q.revenue > p.revenue OR (q.revenue = p.revenue AND q.product < p.product))
) < 3
ORDER BY p.category, p.revenue DESC, p.product;

The pattern works on MySQL 5.7, which has neither window functions nor WITH, so run it against a real table there. The subquery reads the group again for every row, so the work grows quickly with group size. Drop the name comparison to keep ties, as RANK() does.

The PostgreSQL tutorial on window functions explains PARTITION BY, the PostgreSQL window functions reference defines row_number and rank, and the PostgreSQL SELECT reference covers LATERAL and DISTINCT ON. More questions of this shape are in the SQL interview practice guide.

Related questions

The employee with the highest salary in each department is the top 1 per group case, and it has pitfalls of its own. Writing WHERE ROW_NUMBER() OVER (...) <= 3 in one query fails with “window functions are not allowed in WHERE”, which has its own causes and fixes.