How do you use LATERAL to return each customer's three most recent orders?

## The shape of the pattern "Top N rows per group" has a natural LATERAL formulation: one row per group on the left, a per-row query on the right. ```sql SELECT c.id, c.name, o.id AS order_id, o.placed_at, o.total FROM customers c CROSS JOIN LATERAL (SELECT o.id, o.placed_at, o.total FROM orders o WHERE o.customer_id = c.id ORDER BY o.placed_at DESC, o.id DESC FETCH FIRST 3 ROWS ONLY) AS o; ``` Read it as a loop over the left input: for each customer, run the inner query against that customer's orders, sort them newest first, keep three, and emit those three rows paired with the customer's columns. Ten customers yield at most thirty rows. ## Why the limit is per group This is the crux of the question. A row limit written in an ordinary query applies to the query's whole result — `SELECT ... FROM orders ORDER BY placed_at DESC FETCH FIRST 3 ROWS ONLY` gives you three orders in total, not three per customer. Inside a LATERAL derived table the limit belongs to a subquery that is evaluated once per left row, so its scope is that row's matching detail rows. The nesting is what makes the limit per-group; nothing about `FETCH FIRST` itself changed. `FETCH FIRST n ROWS ONLY` is the standard spelling; `LIMIT n` is the common alternative on several engines. Both go inside the parentheses, after the subquery's own `ORDER BY`. ## Where the group list comes from The left side must contain each group exactly once. A `customers` table is the obvious source. If you only have the detail table, generate the groups explicitly: ```sql SELECT g.customer_id, o.id, o.placed_at FROM (SELECT DISTINCT customer_id FROM orders) AS g CROSS JOIN LATERAL (SELECT o.id, o.placed_at FROM orders o WHERE o.customer_id = g.customer_id ORDER BY o.placed_at DESC, o.id DESC FETCH FIRST 3 ROWS ONLY) AS o; ``` If the left side accidentally contains a group twice — a join that fanned out, or a forgotten `DISTINCT` — the LATERAL runs twice for it and you get six rows instead of three. When results look duplicated, count the left side first. ## Ties and determinism `ORDER BY o.placed_at DESC FETCH FIRST 3 ROWS ONLY` is non-deterministic whenever the fourth-newest order shares a timestamp with the third: the engine may return either, and it may return a different one on the next run. Two honest answers exist. Add a unique tiebreaker (`, o.id DESC`) so the ordering is total and exactly three rows come back; or, where the engine supports it, use `FETCH FIRST 3 ROWS WITH TIES`, which keeps every row tied with the last one and can therefore return more than three. Say which behaviour the report needs — an interviewer is listening for the awareness, not for one particular choice. ## Keeping groups with no detail rows As written, a customer with zero orders contributes nothing, because `CROSS JOIN LATERAL` is inner. For a dashboard that must list every customer, use the outer form: ```sql FROM customers c LEFT JOIN LATERAL (...) AS o ON TRUE ``` That emits one NULL-extended row for such a customer. Decide deliberately which behaviour you want; the difference is invisible until someone notices a missing name. ## Variations N = 1 gives "the latest row per group", the most common form of all, and LATERAL handles it without any special casing. The inner query can also compute rather than fetch — an aggregate over the group, or several derived values — since it is an ordinary subquery. And the pattern composes: the result of the LATERAL join can be aggregated, filtered or joined onward like any table. ## A note on alternatives A ranking window function filtered in an enclosing query expresses the same requirement, and correlated subqueries can do it too; those formulations have their own trade-offs and their own place. What is specific to LATERAL is that the per-group limit is written exactly where you would say it in English — sort *this customer's* orders, take three — and that the subquery may return several columns from the chosen rows at once.