I only want the single top row per group; is there a shorter way?

In PostgreSQL, DISTINCT ON (group_column) ... ORDER BY group_column, sort_column is very clean. The portable way is still ROW_NUMBER with a rn = 1 filter, which works on every database.

What happens if I write ORDER BY without PARTITION BY?

Without PARTITION BY, all rows count as a single group and the numbering runs straight through, so you get a table-wide top-N, not "per group." If you want groups, PARTITION BY is required.

Why can't I use the row number directly in WHERE?

Because WHERE runs before the window function is computed. That is why you produce the number in an inner query (or a CTE) and filter it in the outer WHERE.

How do I guarantee exactly N rows when there are ties?

Use ROW_NUMBER; because it gives even equal values distinct numbers, it always returns exactly N rows. If you want to decide which tie comes first, add a second column (such as id) to the ORDER BY.

← All postsThe PerSight Team · Last updated: July 23, 2026 · 8 min read

The Top N Rows per Group in SQL

The three most expensive products in each category, each customer's latest order: finding the top N rows per group with ROW_NUMBER. The window-function pattern and a fallback for MySQL 5.7.

"The three most expensive products in each category," "each customer's latest order," "the best-selling item per store". These are all variations of one pattern: split by a group, sort within each group, take the top N. It is called "top-N per group," and a plain ORDER BY ... LIMIT will not do it, because LIMIT trims the whole result, not each group.

Why isn't a simple LIMIT enough?

ORDER BY price DESC LIMIT 3 gives you the three most expensive products across the entire table; they could all be from a single category. What you want is the top three for each category separately. This is exactly what window functions are for.

How do you build the pattern with ROW_NUMBER?

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) produces a running number starting at 1 within each group. You then filter on that number from the outside. The three most expensive products per category:

SELECT category, name, price
FROM (
  SELECT category, name, price,
         ROW_NUMBER() OVER (
           PARTITION BY category
           ORDER BY price DESC
         ) AS rn
  FROM products
) t
WHERE rn <= 3
ORDER BY category, rn;

Identical syntax on PostgreSQL, SQL Server, Oracle, and MySQL 8+.

PARTITION BY category means "each category on its own"; ORDER BY price DESC gives the most expensive row number 1. The inner query produces the number, the outer query filters the top three with rn <= 3. You cannot use the number directly in WHERE, which is why two layers are needed.

ROW_NUMBER, RANK, or DENSE_RANK?

All three rank, but they treat ties differently. ROW_NUMBER gives even equal values distinct numbers (you get exactly N rows). RANK gives ties the same number and then skips (1,1,3). DENSE_RANK does not skip (1,1,2). If you want "the top 3 prices, ties included," use DENSE_RANK; if you want "exactly 3 rows," ROW_NUMBER is the right pick.

What do I do where there are no window functions, like MySQL 5.7?

Window functions arrived in MySQL 8.0. If you are stuck on an older version, a correlated subquery counts "how many products in the same category are more expensive than this row?" and keeps the ones where that count is below N:

SELECT p.category, p.name, p.price
FROM products p
WHERE (
  SELECT COUNT(*)
  FROM products x
  WHERE x.category = p.category
    AND x.price > p.price
) < 3
ORDER BY p.category, p.price DESC;

A portable fallback for versions without window functions.

This works fine on small tables; on large ones the window function is both faster and more readable. One caveat: ties share the same count, so with equal values you can get more than N rows; if you need exactly N, the window-function pattern is the way to go. If you can, upgrade the database and move to the first pattern.

Frequently asked questions

I only want the single top row per group; is there a shorter way?: In PostgreSQL, DISTINCT ON (group_column) ... ORDER BY group_column, sort_column is very clean. The portable way is still ROW_NUMBER with a rn = 1 filter, which works on every database.
What happens if I write ORDER BY without PARTITION BY?: Without PARTITION BY, all rows count as a single group and the numbering runs straight through, so you get a table-wide top-N, not "per group." If you want groups, PARTITION BY is required.
Why can't I use the row number directly in WHERE?: Because WHERE runs before the window function is computed. That is why you produce the number in an inner query (or a CTE) and filter it in the outer WHERE.
How do I guarantee exactly N rows when there are ties?: Use ROW_NUMBER; because it gives even equal values distinct numbers, it always returns exactly N rows. If you want to decide which tie comes first, add a second column (such as id) to the ORDER BY.

Instead of memorizing this two-layer pattern, you can ask PerSight for "the 3 most expensive products in each category" or "each customer's latest order." It picks the right window function for you and shows you the query it wrote.