What does FETCH FIRST 10 ROWS WITH TIES return that FETCH FIRST 10 ROWS ONLY does not?

## The clause that ends the row limit The standard row-limiting clause ends in one of two keywords: ```sql FETCH FIRST 10 ROWS ONLY FETCH FIRST 10 ROWS WITH TIES ``` `ONLY` is a hard cut: at most ten rows, full stop. `WITH TIES` is a cut plus a rule — take ten rows, then look at the eleventh, twelfth and so on, and keep taking them for as long as their `ORDER BY` key values are equal to those of the tenth row. ## Peers A **peer** of a row is another row that the `ORDER BY` cannot distinguish from it: every sort key compares equal. Peers are exactly the rows whose relative order the sort leaves undefined, which is why cutting a result inside a block of peers is arbitrary — the engine would have to pick some of them and drop others for no reason expressible in the query. `WITH TIES` says: don't pick, take the whole block. ```sql -- scores: 100, 95, 95, 90 SELECT player, score FROM leaderboard ORDER BY score DESC FETCH FIRST 2 ROWS WITH TIES; -- returns 3 rows: 100, 95, 95 ``` With `ONLY`, the same query returns two rows — `100` and *one arbitrary one* of the two 95s. Which 95 you get is not determined by the query, which is precisely the unfairness `WITH TIES` exists to remove. ## Three properties worth stating **The row count becomes an lower-ish bound, not a cap.** `FETCH FIRST 10 ROWS WITH TIES` can return 10, 12, or — in a pathological case where every row shares the sort key — the entire table. Any caller that allocates a fixed-size buffer or renders a fixed-size page must handle an over-sized result. This is the reason `WITH TIES` is a poor fit for paging: page sizes stop being predictable and pages can overlap. **It requires an ORDER BY.** With no sort key there is no notion of a peer, so the clause is not meaningful and engines reject it. **Peers are defined by the ORDER BY columns only** — not by the whole row, and not by anything in the select list. That gives you a precise dial: ```sql -- ties possible: many rows can share a score ORDER BY score DESC FETCH FIRST 10 ROWS WITH TIES -- ties impossible: id is unique, so this returns at most 10 ORDER BY score DESC, id FETCH FIRST 10 ROWS WITH TIES ``` The second form is a common accident: someone adds a unique tie-breaker to make the ordering deterministic, and silently turns `WITH TIES` into `ONLY`. If you want ties included, the sort key must stop at the columns that define "tied". ## When to reach for it - **Leaderboards and award cut-offs** — "top 3, and everyone tied for third" is the textbook case, and business rules often genuinely require it. - **Threshold reports** — "the 20 largest orders, including any that match the 20th value" avoids an unexplainable exclusion in an audit. When you do *not* want it: anything with a fixed page size, anything feeding a UI grid with a known row budget, and anything where an unbounded result would be a problem. ## Doing it without WITH TIES Because support for the clause is uneven, it is worth knowing the portable rewrite. The idea is to compute the cut-off value and then filter on it, which a ranking function expresses directly — `RANK()` assigns equal ranks to peers and skips the following numbers, so keeping rows with rank ≤ n reproduces `WITH TIES` semantics exactly. A two-step formulation without window functions computes the nth value in a subquery and keeps every row at least that large. Either way, you are re-implementing "peers of the boundary row", which is what the clause packages. ## Portability `WITH TIES` is standard SQL, but it is not everywhere: PostgreSQL supports it from version 13, Oracle from 12c, and other engines vary — some implement the row-limiting clause with `ONLY` alone, and some implement no standard row-limiting clause at all. Confirm against your engine's documentation before relying on it, and keep the ranking-function rewrite in mind as the fallback that works wherever window functions do.