⚡ Advancedadvanced

How to get top N rows per group in SQL

Select the top N records within each category or group.

Top N per group using ROW_NUMBERAll databases

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

Assigns a row number within each category ordered by price. Then filters to the top 3.

When to use: Top 3 products per category, latest 5 posts per user, highest score per team.

Latest record per userAll databases

WITH latest AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC
    ) AS rn
  FROM orders
)
SELECT user_id, total, status, created_at
FROM latest
WHERE rn = 1;

Gets the most recent order for each user.

When to use: Getting the latest login, most recent order, last active session.

Top N with ties using RANKAll databases

WITH ranked AS (
  SELECT
    *,
    RANK() OVER (
      PARTITION BY country
      ORDER BY score DESC
    ) AS rnk
  FROM players
)
SELECT *
FROM ranked
WHERE rnk <= 3;

RANK includes ties — if two players share rank 3, both appear. ROW_NUMBER would arbitrarily exclude one.

When to use: When ties should both be included in the top N.

Common Mistakes

→Using ROW_NUMBER when ties should be preserved — use RANK or DENSE_RANK instead
→This pattern requires a CTE or subquery — you cannot use WHERE rn = 1 in the same SELECT

Common Mistakes

Related Topics