SQL Latest Record per Customer

Return the newest complete row for every customer or other business key.

What & Why

The latest-record pattern ranks each entity's rows from newest to oldest and keeps row one. It produces current-state snapshots without losing columns that belong to the chosen record.

See How It Works

BUSINESS QUESTION

Return the latest invoice record for every SaaS account.

id	account_id	amount	status	due_date	paid_at
10	1	240.00	paid	2024-02-01	2024-01-29 13:00:00+00
11	1	260.00	paid	2024-03-01	2024-02-28 16:30:00+00
12	2	90.00	overdue	2024-03-15	NULL
13	3	480.00	open	2024-04-01	NULL

EXAMPLE QUERY

WITH ranked AS (
  SELECT
    id AS invoice_id,
    account_id,
    amount,
    status,
    due_date,
    ROW_NUMBER() OVER (
      PARTITION BY account_id
      ORDER BY due_date DESC, id DESC
    ) AS recency_number
  FROM saas.invoices
)
SELECT invoice_id, account_id, amount, status, due_date
FROM ranked
WHERE recency_number = 1
ORDER BY account_id;

RESULT — latest invoice per SaaS account

invoice_id	account_id	amount	status	due_date
11	1	260.00	paid	2024-03-01
12	2	90.00	overdue	2024-03-15
13	3	480.00	open	2024-04-01

Account 1 keeps invoice 11 because it has the later due date; accounts 2 and 3 keep their only invoices.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice