Technical

A small SQL problem with a big impact: latest row per customer

Published March 9, 2026

This comes up everywhere: you have a table with multiple records per entity (customer, device, account, ticket), and you need the most recent row per group in a way that is correct, fast, and predictable.

The trap: a quick MAX(updated_at) join can return duplicates when timestamps tie, and "fixing" it later can be painful. Below are clean patterns you can use with a reliable tie-break.

Preferred (PostgreSQL, SQL Server, MySQL 8+): window function

Use ROW_NUMBER() and include a deterministic tie-breaker (usually the primary key).

SELECT *
FROM (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY o.customer_id
      ORDER BY o.updated_at DESC, o.id DESC
    ) AS rn
  FROM orders o
) x
WHERE x.rn = 1;

When window functions are not available: MAX join (with care)

This is common on older MySQL versions. It can work, but you must handle ties. If you only join on the max timestamp, you can get multiple rows per customer.

-- Step 1: pick the latest timestamp per customer
SELECT customer_id, MAX(updated_at) AS max_updated_at
FROM orders
GROUP BY customer_id;

If ties matter, add a second step to pick a stable winner (for example, the max ID among rows at the max timestamp).

SELECT o.*
FROM orders o
JOIN (
  SELECT t.customer_id, MAX(t.id) AS max_id
  FROM orders t
  JOIN (
    SELECT customer_id, MAX(updated_at) AS max_updated_at
    FROM orders
    GROUP BY customer_id
  ) m ON m.customer_id = t.customer_id AND m.max_updated_at = t.updated_at
  GROUP BY t.customer_id
) w ON w.max_id = o.id;

Performance notes

bolt Index for the access pattern, e.g. (customer_id, updated_at) (and include id if you tie-break on it).
bolt Always define what "latest" means (timestamp, status priority, ID tie-break).
bolt Validate with edge cases: ties, null timestamps, deleted rows, backfilled data.

Discuss a data problem arrow_forward Back to Newsroom arrow_back