How do I number the rows in each group with ROW_NUMBER?
Number each customer's orders from first to last with ROW_NUMBER, and break ties between orders placed on the same day.
Free course. Your Maestro, an AI, .
The fonda also takes advance orders: the pedidos table has 60 of them, from six regular customers, from January to March 2025 (synthetic data). What was each customer's first order? Their third? To answer, you need a number on each row that says "this is order 1, 2, 3... for this customer." That's what ROW_NUMBER() does:
PARTITION BY cliente: each customer starts counting from 1.ORDER BY fechainsideOVER: the order in which their orders get counted.
With SUM in the previous module, order didn't matter: the total is the same no matter how the rows arrive. For numbering, it does. Without ORDER BY inside OVER, the number each row gets isn't well defined.
ROW_NUMBER() OVER (
PARTITION BY cliente
ORDER BY fecha, pedido_id
) AS num_pedidoWhy pedido_id on line 3? Because Elena has two orders on February 6, 2025: 1014 and 1015. With ORDER BY fecha alone, nothing in your query says which comes first, so don't count on them coming out the same way every time. Adding pedido_id makes the order unique.
Try it
Number each customer's orders from oldest to newest. Return cliente, pedido_id, fecha, monto and num_pedido, and sort the result by cliente, num_pedido. Before you write it, answer the question below. If the numbers don't restart for each customer, ask your Maestro.
Check yourself: Before you run it: imagine the query without the tiebreaker, with just ORDER BY fecha. What numbers do Elena's orders 1014 and 1015, placed on the same day, get?
Number each customer's orders by fecha, and break same-day ties with pedidoid. Return cliente, pedidoid, fecha, monto and numpedido, sorted by cliente, numpedido. You're done when you see 60 rows, each customer runs from 1 to 10, and Elena's orders 1014 and 1015 are 4 and 5.
Your turn
Number each customer's orders by fecha, and break same-day ties with pedido_id. Return cliente, pedido_id, fecha, monto and num_pedido, sorted by cliente, num_pedido. You're done when you see 60 rows, each customer runs from 1 to 10, and Elena's orders 1014 and 1015 are 4 and 5.
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.
Notice that the query has two ORDER BYs, and they do different jobs:
- The one inside
OVER (...)decides the order rows get numbered in. - The one at the end decides the order you see the rows in.
Sometimes the result comes out sorted even without the final one, but that's luck, not a promise. If the order you see the rows in matters, write it at the end.
Row by row
Here are Elena's first six orders from the solution, in Maestro's sandbox. Elena's partition is her 10 orders; ROW_NUMBER() walks through it in fecha, pedido_id order and gives each row its place:
| pedido_id | fecha | monto | num_pedido |
|---|---|---|---|
| 1004 | 2025-01-12 | 530 | 1 |
| 1007 | 2025-01-18 | 2270 | 2 |
| 1009 | 2025-01-23 | 1280 | 3 |
| 1014 | 2025-02-06 | 2390 | 4 |
| 1015 | 2025-02-06 | 1580 | 5 |
| 1028 | 2025-02-21 | 890 | 6 |
When the query reaches Fer's first row, the count goes back to 1. That's all PARTITION BY does here: it decides where the count restarts. Without it, the 60 orders would run from 1 to 60 in a single sequence.
Three cases you'll run into
Newest to oldest. ORDER BY fecha DESC, pedido_id DESC puts number 1 on the newest order. That's how you answer "the latest" instead of "the first," and it's the basis for the next page.
A missing date. If a row has fecha as NULL, it still lands somewhere in the count. In DuckDB, NULLs go last in both ascending and descending order: we tried it in the sandbox with a column that had a NULL, and it came out last both ways. PostgreSQL behaves differently: its documentation says that with DESC it puts NULLs first. If you number "newest to oldest" in PostgreSQL and some dates are missing, number 1 can be a row with no date. When it matters, write NULLS LAST and stop relying on the database's default.
A sort key that repeats. If the columns in your ORDER BY can repeat, add one that doesn't, like the ID. Without it, another run or another database can give you a different number, and nothing warns you.
The interview version: "For each customer, what was their second purchase?" or "number each user's transactions in chronological order." What they're checking is that you partition by the right column, that the inner ORDER BY has a direction and a tiebreaker, and that you know you can't keep row 2 with WHERE (that's the next page). If you want to practice explaining it out loud, ask your Maestro to quiz you.
Outside of interviews, the most common use of ROW_NUMBER() is removing duplicates. If a table got loaded twice and the same customer shows up on several rows, number them per customer from the most recent load to the oldest, and keep number 1:
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY cliente_id
ORDER BY cargado_en DESC, archivo DESC -- with a tiebreaker
) AS n
FROM clientes_cargados;