SQL Interview Prep · Lesson

Keeping the Latest Row Per Key

The 'most recent record per customer' pattern with partition by key, order by date.

Keeping the Latest Row Per Key is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Most-Recent-Per-Key Question

"Return the most recent order for each customer." "Get the latest status for every device." This latest-row-per-key problem is one of the highest-frequency SQL interview tasks because it appears constantly in real analytics work.

It is a specialized top-1-per-group: partition by the key, order by the timestamp descending, and keep the first row. This lesson drills the pattern and its alternatives.

Why MAX Alone Falls Short

A tempting first answer is MAX(order_date) grouped by customer. That gives the latest date, but not the rest of that order's row, the order id, amount, or status.

If the interviewer wants the full latest row, MAX with GROUP BY needs an extra join back to the table on the key and the max date, which is verbose and can break on ties. Window functions are cleaner.

-- Gives the date, not the full row
SELECT customer_id, MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id;

The ROW_NUMBER Pattern

Partition by the key, order by the timestamp descending, and the latest row gets rn = 1. Keep only those rows and you have the full most-recent record per key.

This is the go-to answer. It returns exactly one row per key even when timestamps tie, which is usually what "the latest row" implies.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC
    ) AS rn
  FROM orders
)
SELECT customer_id, order_id, order_date, amount
FROM ranked
WHERE rn = 1;

Breaking Timestamp Ties

Two orders for the same customer can share an order_date (same day, or identical timestamps). Without a tiebreaker, which one becomes rn = 1 is arbitrary and may change between runs.

Add a unique secondary key such as order_id DESC so the latest row is deterministic. Interviewers specifically probe whether you noticed this edge case.

ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY order_date DESC, order_id DESC
) AS rn

Latest vs All Ties

Decide what "latest" means when timestamps tie:

Want exactly one row per key → ROW_NUMBER with a tiebreaker.
Want all rows that share the maximum timestamp → use RANK() = 1 instead, which returns every tied latest row.

Asking this clarifying question signals you understand the semantics, not just the syntax.

WITH ranked AS (
  SELECT *,
    RANK() OVER (
      PARTITION BY customer_id ORDER BY order_date DESC
    ) AS rnk
  FROM orders
)
SELECT * FROM ranked WHERE rnk = 1;

The Correlated Subquery Alternative

Before window functions were universal, the latest-per-key answer used a correlated subquery: keep a row only if no other row for the same key has a greater date.

It works but runs the inner query per row, so it is slower on large tables and awkward with ties. Mention it to show range, but prefer the window-function answer for performance.

SELECT o.*
FROM orders o
WHERE o.order_date = (
  SELECT MAX(o2.order_date)
  FROM orders o2
  WHERE o2.customer_id = o.customer_id
);

Postgres DISTINCT ON Shortcut

PostgreSQL offers a concise idiom: DISTINCT ON (key) keeps the first row per key according to the ORDER BY. The ORDER BY must lead with the same key columns, then the tiebreak/timestamp.

It is elegant and fast in Postgres, but non-portable. Note it as a dialect-specific bonus while keeping ROW_NUMBER as your portable default.

SELECT DISTINCT ON (customer_id)
  customer_id, order_id, order_date, amount
FROM orders
ORDER BY customer_id, order_date DESC, order_id DESC;

Latest Row With a Condition

Real questions add filters: "the most recent completed order per customer". Apply the filter before ranking so only qualifying rows are numbered.

Put the condition in the WHERE of the inner query (it runs before the window function), then take rn = 1 in the outer query. Filtering after ranking would give the wrong row.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC
    ) AS rn
  FROM orders
  WHERE status = 'completed'
)
SELECT * FROM ranked WHERE rn = 1;

Worked Example: Latest Device Status

A status_log table records device_id, status, and logged_at. To get each device's current status, partition by device_id, order by logged_at DESC, keep rn = 1.

This is the engine behind dashboards showing the "current state" of many entities from an append-only event log. The same recipe powers latest-price, latest-location, and latest-version queries.

WITH latest AS (
  SELECT device_id, status, logged_at,
    ROW_NUMBER() OVER (
      PARTITION BY device_id ORDER BY logged_at DESC
    ) AS rn
  FROM status_log
)
SELECT device_id, status, logged_at
FROM latest
WHERE rn = 1;

Performance Notes

Talking points that earn senior credit:

An index on (customer_id, order_date DESC) lets the engine read the latest row per key efficiently.
The window approach scans the table once; the correlated subquery does not.
DISTINCT ON in Postgres can use the same index and is often the fastest single-table option.
For append-heavy event logs, consider a materialized "latest" table refreshed incrementally.

Common Mistakes

Watch for these:

Using MAX(date) and returning only the date, not the full row.
Forgetting the tiebreaker, yielding nondeterministic results when dates tie.
Filtering on the condition after ranking, which can pick a row that should have been excluded.
Confusing "latest one row" (ROW_NUMBER) with "all latest tied rows" (RANK).

Quick Check

Choose the correct latest-row-per-key query.

Recap: Latest Row Per Key

The pattern: PARTITION BY key, ORDER BY timestamp DESC (plus a unique tiebreaker), keep rn = 1.

MAX(date) gives the date, not the full row.
Always add a tiebreaker for determinism.
Use RANK() = 1 if you want all rows tied at the latest timestamp.
Filter conditions belong in the inner query, before ranking.
Postgres DISTINCT ON is a concise, fast dialect-specific alternative.

Frequently asked questions

Is the “Keeping the Latest Row Per Key” lesson free?

Yes — the full text of “Keeping the Latest Row Per Key” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Keeping the Latest Row Per Key”?

The 'most recent record per customer' pattern with partition by key, order by date. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Keeping the Latest Row Per Key” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep