Latest Record Per Group

Hook: This is the SQL version of picking the newest card from each stack: it looks simple, but ties and duplicates can quietly ruin the result.

Question: How do you get the latest record per group in SQL?

Answer: Use a window function such as ROW_NUMBER() to number rows inside each group after sorting them from newest to oldest. Then keep only the row with rn = 1 for each group. This is usually better than joining on MAX(date) because you can add a tie-breaker, like an id, so the result is deterministic.

Interview-Ready Answer: I would solve this with ROW_NUMBER() over a PARTITION BY on the group key and an ORDER BY on the timestamp descending, plus a second sort key for ties. Then I filter to rn = 1. That gives exactly one latest row per group and avoids the common bug where a MAX() join returns duplicate rows when two records share the same timestamp.

What this pattern really means

When interviewers say latest record per group, they mean: for each group key, return the single row that should be considered newest by some ordering column such as created_at, updated_at, or an auto-incrementing id. A window function is a function that looks across a set of related rows without collapsing them into one row, which is why it is perfect here.

How ROW_NUMBER() works under the hood

1. Split the rows into groups. PARTITION BY customer_id means each customer gets its own mini-set of rows.
2. Sort inside each group. ORDER BY created_at DESC, order_id DESC puts the newest row first. The second key is the tie-breaker, which makes the result deterministic when two rows share the same timestamp.
3. Assign numbers. The first row in each partition gets 1, the next gets 2, and so on.
4. Filter to the winner. Keeping only rn = 1 returns the latest row from each group.
5. Return full rows, not just the date. Because the row number is attached to the original row, you keep every column: status, amount, user id, anything.
6. Let the engine do the heavy lifting. The database usually sorts the data, computes the numbering, then discards everything except the top row in each partition.

Why not just use MAX()?

The classic mistake is to find the latest timestamp with MAX(created_at) and then join back to the table. That can work if timestamps are unique, but it breaks when two rows share the same max value. Suddenly you get two rows for one group, which is wrong if the business wants exactly one latest record.

Memory hook: think of each group as a race lane. Sort the runners by finish time, and ROW_NUMBER() hands the gold medal to lane 1.

When to use it

Use this pattern when you need the latest order per customer, the newest status per ticket, the most recent login per user, or the freshest sensor reading per device. It is especially useful in dashboards, audit trails, and event streams where every entity accumulates many history rows.

Performance and complexity

In practice, the engine must inspect all candidate rows, so the work is usually O(n log n) because of sorting, with memory usage that can grow with the size of each partition. On large tables, the database may spill to disk if the sort does not fit in memory. A useful index is often (group_key, sort_key DESC, tie_breaker DESC), because it can reduce sorting work or allow a more efficient ordered scan. If the table has millions of rows and thousands of groups, that index can be the difference between a fast index-assisted plan and a slow temp-file sort.

Important edge cases

• Equal timestamps: add a deterministic tie-breaker such as id, version, or sequence number.
• NULL values: decide whether NULL means oldest, newest, or invalid; do not leave it to default ordering unless that is truly the business rule.
• Multiple latest rows are desired: use RANK() or DENSE_RANK() instead of ROW_NUMBER().
• No row exists for a group: if you need empty groups too, you usually start from a groups table and LEFT JOIN the ranked result.

Mentally, the process is simple: sort inside the group, number the rows, keep number one. That is the whole pattern.

Real-world story

Imagine a checkout service for an e-commerce app. Every order has many status rows: paid, packed, shipped, delivered. The customer support screen needs the current status for each order, not the whole history. The backend query uses the latest-record-per-group pattern to show one row per order id.

What goes wrong when this is misunderstood? A team uses MAX(updated_at) and joins back to the status table. One day, two status updates land in the same second for a busy order, so the join returns two rows. The UI starts showing duplicate cards, metrics count the order twice, and support sees logs like expected 1 row, got 2. The bug looks random, but the root cause is that the query never guaranteed a single winner.

Follow-up & Tricky Questions:

• How would you write this in PostgreSQL? You can still use ROW_NUMBER(), or use DISTINCT ON (group_key) with a matching ORDER BY. DISTINCT ON is shorter, but it is PostgreSQL-specific.
• What index helps most? A composite index starting with the group key and then the sort keys, for example (customer_id, created_at DESC, order_id DESC). That gives the optimizer a better chance to read rows in the right order.
• How do you handle NULL timestamps? Decide the business rule first. If NULL should be ignored, filter it out; if it should count as oldest or newest, use explicit COALESCE or a database-specific NULLS FIRST/LAST rule.
• How do you get the latest three rows per group? Keep ROW_NUMBER() and filter to rn <= 3. That is the same pattern, just a wider slice of each partition.
• Can you do this without window functions? Yes, but it is usually uglier and less safe. You typically need a self-join or an aggregate join-back, and tie handling becomes the hard part.
• Does RANK() = 1 mean the same thing as ROW_NUMBER() = 1? No. RANK() returns all tied rows at the top, while ROW_NUMBER() forces a single row when you give it a full ordering.
• Does MAX(created_at) alone give the latest row? No. It gives only the timestamp value, not the full row, and it can match more than one row when timestamps tie.
• What happens if two rows tie on every sort key? The result is still ambiguous unless you add another stable tie-breaker, such as a primary key or sequence number.

Tricky / gotcha questions:

• If two rows have the same latest timestamp, which one wins? Whichever row your full ORDER BY places first. Without a tie-breaker, the result is not guaranteed to be stable.
• Can GROUP BY return the whole latest row? Not by itself. GROUP BY collapses rows, so you lose the other columns unless you join back or use a window function.
• Is the latest row always the one with the greatest id? Only if the id is designed to increase in time order. Many systems use UUIDs or distributed ids where that assumption is false.

Common Mistakes:

• Using MAX() and stopping there. Correction: MAX() gives only a value, not the full row; you still need a join or a window function.
• Forgetting a tie-breaker. Correction: always add a second sort key, usually a primary key or sequence column, so the winner is deterministic.
• Picking RANK() when you want one row. Correction: use ROW_NUMBER() for exactly one latest row per group.
• Assuming the database order is stable. Correction: if your ORDER BY does not fully break ties, the engine is free to choose any tied row.

Memory Hook: Partition, sort, number, keep one. Picture each group as a small queue where the newest person stands first and only the first person gets selected.

Cheat Sheet:

• ROW_NUMBER() is the standard answer for one latest row per group.
• PARTITION BY defines the group.
• ORDER BY ... DESC puts the newest row first.
• Always add a tie-breaker column after the timestamp.
• rn = 1 keeps only the winner.
• For ties you want to keep, use RANK() or DENSE_RANK().

Practice Tasks:

• Write a query to get the latest order per customer using your own sample table.
• Modify it to return the latest 3 rows per customer with rn <= 3.
• Test a tie case with the same timestamp and prove why a tie-breaker is needed.