Why does a top-3-per-customer query built from a correlated COUNT return four rows for some customers?

## The idiom Before window functions were widely available, top-N-per-group was written with a correlated count: a row belongs to the top N of its group when fewer than N rows of that group beat it. ```sql SELECT o.* FROM orders o WHERE (SELECT COUNT(*) FROM orders o2 WHERE o2.customer_id = o.customer_id AND o2.order_date > o.order_date) < 3; ``` The subquery is correlated twice over: on `o.customer_id`, which confines the count to the same customer, and on `o.order_date`, which is the ordering key. Reading it as "how many of this customer's orders are newer than this one" makes the predicate obvious — the newest order has 0, the second 1, the third 2, and everything else 3 or more. ## Why ties produce extra rows The count is a function of the *values* compared, not of row identity. If a customer's 3rd and 4th newest orders share the same `order_date`, both have exactly two strictly-later orders, so both satisfy `< 3` and four rows come back. Push the tie further up — two orders on the newest date — and both score 0, the next scores 2, and you still get four rows overall for that customer. This behaviour is *rank-like*: rows tied on the ordering key are treated as occupying the same position, exactly as a rank would. It is not a bug in the idiom, it is the consequence of comparing on a non-unique key. Whether you want it depends on the requirement: "the three newest orders" wants exactly three, while "all orders in the three newest positions" wants the ties kept. ## Getting exactly N Make the ordering total by adding a unique column as a secondary comparison inside the correlated predicate: ```sql SELECT o.* FROM orders o WHERE (SELECT COUNT(*) FROM orders o2 WHERE o2.customer_id = o.customer_id AND (o2.order_date > o.order_date OR (o2.order_date = o.order_date AND o2.order_id > o.order_id))) < 3; ``` Now no two rows of the same customer can have the same count, because `(order_date, order_id)` is unique, so each customer contributes at most three rows and the choice among tied dates is deterministic. Some engines let you write that condition as a row-value comparison, `(o2.order_date, o2.order_id) > (o.order_date, o.order_id)`; the expanded `OR` form above is the portable spelling. ## Why `>` and not `>=` With `>=` the inner query also counts the outer row itself, since a row trivially satisfies `o2.order_date >= o.order_date` when `o2` is that same row. Every count shifts up by one and `< 3` yields only the top two per customer. Candidates get this backwards constantly. If you prefer to think in ranks, `>=` gives you "the rank of this row" (1-based) and the predicate becomes `<= 3`; `>` gives you "how many beat it" (0-based) and the predicate is `< 3`. Pick one convention and be consistent. ## The top-1 special case For N = 1 the usual spelling is an equality against a correlated aggregate: ```sql SELECT o.* FROM orders o WHERE o.order_date = (SELECT MAX(o2.order_date) FROM orders o2 WHERE o2.customer_id = o.customer_id); ``` Same tie behaviour — every order sharing the customer's maximum date comes back — plus a NULL trap: rows whose `order_date` is NULL never satisfy the equality, because `NULL = anything` is unknown, so those orders are simply absent from the result rather than sorted to one end. ## When to use this shape at all On an engine with window functions, ranking in a derived table is the clearer expression of the same intent and it states tie handling explicitly by the choice of ranking function. The correlated-count idiom still earns its place in two situations: engines or contexts without window support, and cases where N is not a constant but depends on the group. Being able to read it matters regardless — it is common in older code, and misreading its tie behaviour is how "why does this report show four rows" tickets happen. ## Common mistakes - Assuming the predicate guarantees exactly N rows per group. - Using `>=` and quietly returning N−1 rows. - Forgetting the `o2.customer_id = o.customer_id` correlation, which turns a per-customer top-N into a global one. - Ignoring NULLs in the ordering key in the `MAX` variant.