Top-N per group query in SQL

Introduction

To find the top N items in each group from a table, like the best scores per class or highest sales per region.

Finding the top 3 selling products in each category.

Listing the top 5 students by grade in each school.

Getting the most recent 2 orders per customer.

Showing the highest paid employees in each department.

Syntax

SQL

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY order_column DESC) AS rn
  FROM table_name
) sub
WHERE rn <= N;

ROW_NUMBER() assigns a unique rank number within each group.

PARTITION BY divides rows into groups to rank separately.

Examples

Finds top 3 products by sales in each category.

SQL

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM products
) sub
WHERE rn <= 3;

Finds the highest paid employee in each department.

SQL

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) sub
WHERE rn <= 1;

Gets the 2 most recent orders per customer.

SQL

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
) sub
WHERE rn <= 2;

Sample Program

This query finds the top 2 products by sales amount in each region.

SQL

CREATE TABLE sales (
  region VARCHAR(20),
  product VARCHAR(20),
  amount INT
);

INSERT INTO sales VALUES
('North', 'Apples', 100),
('North', 'Oranges', 150),
('North', 'Bananas', 120),
('South', 'Apples', 200),
('South', 'Oranges', 180),
('South', 'Bananas', 160);

SELECT region, product, amount FROM (
  SELECT region, product, amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
  FROM sales
) sub
WHERE rn <= 2
ORDER BY region, rn;

Important Notes

ROW_NUMBER() starts counting at 1 for each group.

If you want ties to have the same rank, consider RANK() or DENSE_RANK() instead.

Always order the final result if you want consistent output.

Summary

Use ROW_NUMBER() with PARTITION BY to rank rows within groups.

Filter by rank to get top N rows per group.

This helps find best or most recent items in each category or group.