How to Get the Top N Rows per Group in MySQL

To return the top few rows inside every group in MySQL, number rows within each group with ROW_NUMBER(), then filter those numbers in an outer query. For example, to return the two most expensive products in every category, use PARTITION BY category_id to restart numbering for each category.

GROUP BY followed by LIMIT returns the top groups overall; it does not return N rows from every group. Window functions solve the per-group case without collapsing the detail rows. MySQL supports window functions and common table expressions in MySQL 8.0 and later. See the MySQL manuals for window function syntax and common table expressions.

Sample table

The examples use a products table with products assigned to categories:

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  category_id INT NOT NULL,
  product_name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

INSERT INTO products (product_id, category_id, product_name, price)
VALUES
  (1, 1, 'Laptop', 1200.00),
  (2, 1, 'Desktop', 1200.00),
  (3, 1, 'Monitor', 350.00),
  (4, 1, 'Dock', 350.00),
  (5, 1, 'Keyboard', 80.00),
  (6, 2, 'Phone', 900.00),
  (7, 2, 'Tablet', 600.00),
  (8, 2, 'Charger', 30.00);

Get exactly N rows per group

Use ROW_NUMBER() when you need at most N rows per category. The unique product_id breaks equal-price ties consistently:

WITH ranked_products AS (
  SELECT
    product_id,
    category_id,
    product_name,
    price,
    ROW_NUMBER() OVER (
      PARTITION BY category_id
      ORDER BY price DESC, product_id
    ) AS row_num
  FROM products
)
SELECT category_id, product_id, product_name, price
FROM ranked_products
WHERE row_num <= 2
ORDER BY category_id, row_num;

+-------------+------------+--------------+---------+
| category_id | product_id | product_name | price   |
+-------------+------------+--------------+---------+
|           1 |          1 | Laptop       | 1200.00 |
|           1 |          2 | Desktop      | 1200.00 |
|           2 |          6 | Phone        |  900.00 |
|           2 |          7 | Tablet       |  600.00 |
+-------------+------------+--------------+---------+

PARTITION BY category_id starts the row numbers over for each category. The window’s ORDER BY chooses which products receive the first row numbers; the final ORDER BY sorts the returned rows. MySQL documents that ROW_NUMBER() is nondeterministic without an ordering clause, so include a unique tie-breaker when you require repeatable exact-N results. See the ROW_NUMBER() and ranking function descriptions.

To return the top three rows per category, change the outer condition to WHERE row_num <= 3.

Include ties at the cutoff

If a tie should return more than N rows, use RANK() and do not add a unique key to its window ordering. For example, this assigns the same rank to products with the same price:

WITH ranked_products AS (
  SELECT
    product_id,
    category_id,
    product_name,
    price,
    RANK() OVER (
      PARTITION BY category_id
      ORDER BY price DESC
    ) AS price_rank
  FROM products
)
SELECT category_id, product_id, product_name, price, price_rank
FROM ranked_products
WHERE price_rank <= 2
ORDER BY category_id, price DESC, product_id;

In this sample, category 1 has two products tied for the highest price, so both have rank 1. Because RANK() leaves gaps after ties, the next price has rank 3. If a tie occurs at rank 2, WHERE price_rank <= 2 returns all products tied at that cutoff, even if the result has more than two rows.

Use DENSE_RANK() instead when N means the top N distinct price values. Unlike RANK(), DENSE_RANK() does not leave gaps after tied rows. This query returns products from the two highest distinct price levels in each category:

WITH ranked_products AS (
  SELECT
    product_id,
    category_id,
    product_name,
    price,
    DENSE_RANK() OVER (
      PARTITION BY category_id
      ORDER BY price DESC
    ) AS price_level
  FROM products
)
SELECT category_id, product_id, product_name, price, price_level
FROM ranked_products
WHERE price_level <= 2
ORDER BY category_id, price DESC, product_id;

For category 1 in the sample data, this includes both products priced at 1200 and both products priced at 350. MySQL describes the tie and gap rules in its ranking function reference.

Rank grouped totals

You can also aggregate first and then rank the groups. This query returns the three customers with the highest sales in each region:

WITH customer_totals AS (
  SELECT region_id, customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY region_id, customer_id
), ranked_customers AS (
  SELECT
    region_id,
    customer_id,
    total_sales,
    ROW_NUMBER() OVER (
      PARTITION BY region_id
      ORDER BY total_sales DESC, customer_id
    ) AS row_num
  FROM customer_totals
)
SELECT region_id, customer_id, total_sales
FROM ranked_customers
WHERE row_num <= 3
ORDER BY region_id, row_num;

The first CTE calculates one total per region and customer. The second assigns a row number to those totals within each region. This is different from applying LIMIT 3 after GROUP BY, which would return only three customers across the entire result.

MySQL 5.7 and earlier

MySQL 5.7 does not support window functions. A correlated subquery can count how many products rank ahead of each candidate. This example returns the same two rows per category, using product_id to break price ties:

SELECT
  p1.category_id,
  p1.product_id,
  p1.product_name,
  p1.price
FROM products AS p1
WHERE (
  SELECT COUNT(*)
  FROM products AS p2
  WHERE p2.category_id = p1.category_id
    AND (
      p2.price > p1.price
      OR (p2.price = p1.price AND p2.product_id < p1.product_id)
    )
) < 2
ORDER BY p1.category_id, p1.price DESC, p1.product_id;

For a different N, change < 2 to < N. This approach compares each candidate with rows in its own group, so it can do more work on large tables than the window-function query. Check the execution plan with EXPLAIN and benchmark it against your data.

Which ranking function should you use?

• Use ROW_NUMBER() for exactly N rows per group, with a unique tie-breaker for repeatable results.
• Use RANK() to include ties at the Nth rank; tied rows share a rank and later ranks can have gaps.
• Use DENSE_RANK() for the top N distinct ordering values, with no rank gaps.

For function syntax, see the MySQL ROW_NUMBER() reference and the MySQL common table expression guide.