How do I get the latest row for each group in SQL?
Keep one row per customer, the most recent, with QUALIFY or WITH, and see why WHERE can't do it.
Free course. Your Maestro, an AI, .
It's one of the most common questions in data work: for each customer, what was their most recent order? Not just the date: the whole order, with its number and amount. MAX(fecha) with GROUP BY gets you the date, but not the rest of the row.
The recipe has two steps: number each customer's orders from newest to oldest, and keep number 1. The second step is where almost everyone trips up the first time. Open the example one step at a time, and try each step before you look.
Each customer's latest order
Worked example · 4 steps
The pedidos table has 60 orders from six customers. We want one row per customer: their most recent order, with pedido_id, fecha and monto. Carla has two orders on March 31, 2025; we keep the one with the higher pedido_id.
Work out the next step yourself, then open it to compare.
Both versions return the same six rows: we checked in Maestro's sandbox. Before you pick one, it's worth seeing why the approach almost everyone tries first breaks.
The MAX approach and why it breaks
Without windows, the classic recipe is to get each customer's latest date with GROUP BY and join it back to the table:
WITH ultima AS (
SELECT cliente, MAX(fecha) AS fecha
FROM pedidos
GROUP BY cliente
)
SELECT p.cliente, p.pedido_id, p.fecha, p.monto
FROM pedidos AS p
JOIN ultima AS u
ON p.cliente = u.cliente AND p.fecha = u.fecha
ORDER BY p.cliente, p.pedido_id;In the sandbox it returned 7 rows, not 6. Carla showed up twice:
| cliente | pedido_id | fecha | monto |
|---|---|---|---|
| Carla | 1059 | 2025-03-31 | 330 |
| Carla | 1060 | 2025-03-31 | 2050 |
Both rows have Carla's latest date, so both survive the join. The query isn't wrong: "the latest order" just isn't well defined when two orders share a date. With ROW_NUMBER() and pedido_id DESC in the ORDER BY, you decide which one wins, and you always get one row per customer.
What does work, and in which database
The three versions below returned the same six rows in the sandbox (DuckDB 1.5.6). QUALIFY also accepted the window's alias: with ROW_NUMBER() OVER (...) AS n in the SELECT, QUALIFY n = 1 gave the same result.
SELECT cliente, pedido_id, fecha, monto
FROM pedidos
QUALIFY ROW_NUMBER() OVER (
PARTITION BY cliente
ORDER BY fecha DESC, pedido_id DESC
) = 1
ORDER BY cliente;SELECT cliente, pedido_id, fecha, monto
FROM (
SELECT cliente, pedido_id, fecha, monto,
ROW_NUMBER() OVER (
PARTITION BY cliente
ORDER BY fecha DESC, pedido_id DESC
) AS n
FROM pedidos
) AS numerados
WHERE n = 1
ORDER BY cliente;SELECT DISTINCT ON (cliente) cliente, pedido_id, fecha, monto
FROM pedidos
ORDER BY cliente, fecha DESC, pedido_id DESC;Which version to use depends on the database you use at work. Pick yours; what each tab says comes from that database's official documentation, linked below.
All three versions work: QUALIFY, the subquery (or WITH) and DISTINCT ON. All three returned the same six rows in Maestro's sandbox. DuckDB's documentation says QUALIFY filters the results of window functions and avoids the need for a subquery or WITH.
Watch the NULLs: DuckDB puts them last with DESC too, so a missing date doesn't win number 1.
PostgreSQL 18's SELECT documentation doesn't list QUALIFY among its clauses. Its window functions tutorial says they aren't allowed in WHERE, and that to filter after they're calculated you use a sub-select: the subquery version or the WITH one.
PostgreSQL documents DISTINCT ON, and warns that the first row of each group is unpredictable unless an ORDER BY decides it.
Watch the NULLs: with DESC, PostgreSQL puts them first. If some dates are missing, write ORDER BY fecha DESC NULLS LAST.
BigQuery's query syntax includes a QUALIFY clause, written after HAVING, and its evaluation order puts it after the windows. The QUALIFY version is the shortest.
If your QUALIFY query throws an error you don't understand, the subquery version doesn't depend on QUALIFY and does the same thing.
Snowflake's documentation describes QUALIFY as the filter on window function results, and says it's evaluated after they're calculated. Its example filters on the window's alias, as in QUALIFY row_num = 1.
The subquery version works too, if your team prefers it because it's portable.
Your turn, with a twist
The example already gave you each customer's latest order. The exercise asks for that and something the example doesn't do: on the same row, how much that customer spent across the whole quarter. You can use QUALIFY, WITH or a subquery: they all get marked the same if they return the same rows. Before you run it, answer the two questions below. If your total comes out equal to the last order's amount, ask your Maestro why. If your database at work doesn't accept any of these versions, ask how to adapt it.
Check yourself: Before you run it: if you use RANK() OVER (PARTITION BY cliente ORDER BY fecha DESC) instead of ROW_NUMBER, with no pedido_id, and keep rank 1, how many rows do you get?
Check yourself: Before you run it: if the same query calculates SUM(monto) OVER (PARTITION BY cliente) and filters with QUALIFY, what number shows up in total_cliente on Carla's row?
Return one row per customer with their most recent order (cliente, pedidoid, fecha, monto) and, on the same row, totalcliente: how much that customer spent on all of their orders in the quarter. If two orders fall on the same day, keep the one with the higher pedidoid. Sort by cl
Your turn
Return one row per customer with their most recent order (cliente, pedido_id, fecha, monto) and, on the same row, total_cliente: how much that customer spent on all of their orders in the quarter. If two orders fall on the same day, keep the one with the higher pedido_id. Sort by cliente. You're done when you see 6 rows and Carla's says 1060 and 11910.
Tables: pedidos (pedido_id, cliente, fecha, monto)
pedidos · 60 rows
| Column | Type |
|---|---|
| pedido_id Número del pedido; crece con el orden en que se registró. | BIGINT |
| cliente Nombre del cliente. | VARCHAR |
| fecha Día del pedido, sin hora. | DATE |
| monto Monto del pedido, en pesos. | BIGINT |
First rows
| pedido_id | cliente | fecha | monto |
|---|---|---|---|
| 1001 | Fer | 2025-01-03 | 850 |
| 1002 | Diego | 2025-01-04 | 1360 |
| 1003 | Beto | 2025-01-11 | 600 |
Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.
Ready to run your query.
Check yourself: You wrote WHERE ROW_NUMBER() OVER (PARTITION BY cliente ORDER BY fecha DESC) = 1 and DuckDB answers WHERE clause cannot contain window functions. What's going on?
Before you use this at work
Where you put the sum matters. If you calculate total_cliente in the outer query of a WITH, after WHERE n = 1, the window only sees the row that's left: in a sandbox run, Carla came out with 2050 instead of 11910. Anything that needs all the rows gets calculated before you filter.
The top N, not just the latest. Change = 1 to <= 3 and you get each customer's three most recent orders. Same query, different number.
A missing date can win. In a database that puts NULLs first with DESC, like PostgreSQL, an order with no date would come out as "the most recent." DuckDB puts them last, so it doesn't happen there. Write NULLS LAST and the query means the same thing in both.
In an interview it comes in disguise: "return each user's most recent transaction," "dedupe this table, keeping the newest record" or "the latest status of each order." It's the same problem all three times. What they're checking is that you don't try it in WHERE, that you think about ties, and that you can explain why MAX with GROUP BY isn't enough when you want the whole row. If you have a history table at work and want the latest state of each item, tell your Maestro its columns.
A check worth doing: find one of your queries that uses MAX(fecha) and then a join to get the row back. Run it and count the rows per group: if a group shows up twice, now you know why, and how to fix it.