lesson 40 Window functions · advanced

Latest row per group

Keep the most recent record for each group.

the idea

Use a CTE with ROW_NUMBER sorted newest-first, then filter to row number 1. A window result cannot be filtered by WHERE in the same query block.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

orders6 columns · 12 rows

idinteger · key	customer_idinteger	product_idinteger	quantityinteger	statustext	ordered_attext
101	1	1	2	paid	2025-01-01
102	2	3	1	paid	2025-01-02
103	1	2	1	paid	2025-01-05
104	3	5	2	pending	2025-01-08
105	4	4	5	paid	2025-02-01

showing 5 of 12 rows

products4 columns · 6 rows

idinteger · key	nametext	categorytext	pricereal
1	Notebook	Stationery	12
2	Desk lamp	Home	45
3	Keyboard	Tech	80
4	Pen set	Stationery	8
5	Headphones	Tech	120

showing 5 of 6 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

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

Watch for: MAX(date) plus an unrelated id does not reliably select the entire latest row.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
See multiple rows per customer. Customers can have several orders. Choosing the latest one needs an order and a way to keep only rank 1.
step-1.sql
sql›
```
SELECT id, customer_id, ordered_at
FROM orders
ORDER BY customer_id, ordered_at DESC, id DESC
LIMIT 12;
```

Keep the newest row per group. ROW_NUMBER ordered by newest first marks the latest order as 1. Filtering rn = 1 keeps one row per customer.

step-2.sql

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