Top N per group and deduplication

01The problem

Three requests that look different but are the same problem:

1. Sales: "Our top three customers for each transport mode, by revenue."
2. Finance: "For each shipment, the most recent payment."
3. Data team: "The CRM export has some customers in it twice. Keep one row per customer: the newest."

ORDER BY … LIMIT 3 gives the top three overall, not per mode. GROUP BY with MAX(payment_date) finds the latest date but loses the rest of the row (the amount, the method). Each request needs "the top N rows within each group, with the whole row".

02The concept

The pattern has three steps:

1. Number the rows within each group with a window function, in the order that defines "top".
2. Do it in a CTE or subquery, because WHERE can't see window functions in the same query.
3. Filter on the number outside.

WITH ranked AS (
  SELECT …, ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col DESC, id) AS rn
  FROM …
)
SELECT … FROM ranked WHERE rn <= 3;

Choosing the numbering function decides what happens to ties:

Always add a tie-breaker to ROW_NUMBER (usually the ID). Without one the database may pick a different row each time you run the query, and two people running the same report get different answers.

03Example

The top three customers by delivered freight charges, within each mode:

WITH customer_mode AS (
  SELECT r.mode, s.customer_id, SUM(s.freight_charge) AS charges
  FROM shipments AS s
  JOIN routes AS r ON r.route_id = s.route_id
  WHERE s.status = 'Delivered'
  GROUP BY r.mode, s.customer_id
),
ranked AS (
  SELECT
    mode,
    customer_id,
    charges,
    ROW_NUMBER() OVER (PARTITION BY mode ORDER BY charges DESC, customer_id) AS rn
  FROM customer_mode
)
SELECT ranked.mode, ranked.rn, c.company_name, ranked.charges
FROM ranked
JOIN customers AS c ON c.customer_id = ranked.customer_id
WHERE ranked.rn <= 3
ORDER BY ranked.mode, ranked.rn;

Nine rows: three per mode. Note the order of the work. First total per customer and mode, then number within each mode, then filter, and only then join the names in. Joining names at the end means the ranking runs on fewer, narrower rows.

04Walkthrough

1. Run the example. Then change rn <= 3 to rn = 1 to get each mode's single biggest customer.
2. Replace ROW_NUMBER() with RANK(). Nothing changes here because no two customers have identical charges, but on a column with ties, such as containers, RANK could return more than three rows per group.
3. Now the "latest record" version. For shipments paid in instalments, keep only the most recent payment, with the full row:

WITH numbered AS (
  SELECT
    p.*,
    ROW_NUMBER() OVER (PARTITION BY shipment_id ORDER BY payment_date DESC, payment_id DESC) AS rn,
    COUNT(*) OVER (PARTITION BY shipment_id) AS payments
  FROM payments AS p
)
SELECT shipment_id, payment_id, payment_date, amount, method, payments
FROM numbered
WHERE rn = 1 AND payments > 1
ORDER BY shipment_id
LIMIT 20;

4. Check the count without LIMIT: 159 shipments have more than one payment. Deduplication works the same way: number the copies newest first and keep rn = 1.

05Practice

06Challenge

07More practice

08Check your understanding