PostgreSQL: LIMIT vs FETCH FIRST ROWS ... WITH TIES

The Standard Way to Limit Results

Most SQL developers reach for LIMIT when they need to cap the number of rows returned by a query. It is concise and widely supported, but it is not part of the SQL standard. The portable alternative is FETCH FIRST ROWS, which works in PostgreSQL and other compliant databases. Beyond basic row counting, this syntax offers capabilities that LIMIT lacks, particularly around handling duplicate values.

Consider a small table with seven rows. Using LIMIT returns the first three rows found, with no guarantee of order:

PgSQL

The same result can be achieved with the standards-compliant syntax:

PgSQL

One subtle trap applies to both forms: if you pass NULL as the row count, PostgreSQL does not treat it as zero. Since NULL is undefined, the database has no bound and returns all rows:

PgSQL

Handling Ties with WITH TIES

PostgreSQL 13 introduced WITH TIES, which addresses a common issue: what happens when the cutoff row has duplicates? A plain FETCH FIRST 3 ROWS stops after three rows even if the third value also appears in a fourth row. With WITH TIES, PostgreSQL includes all rows that share the same sort key as the last returned row:

PgSQL

Here the query returns four rows because the value at the boundary repeats. An ORDER BY clause is required for meaningful behavior — without it, the result set is arbitrary. The inclusion rule is not about counting distinct values; it only adds rows that are identical to the last one in the sort order. If an extra row makes the boundary unique, the query returns exactly the requested number of rows:

PgSQL

Multi-Column ORDER BY Behavior

Real-world tables rarely have a single column, so it matters how WITH TIES behaves when additional columns are present. The tie-breaking logic applies to the complete ORDER BY expression list. If you order by id, rows with the same id are considered ties and are all included:

PgSQL

In this case, five rows are returned because id = 3 appears multiple times. When the ORDER BY clause includes two columns, a row is only added if both columns match the boundary row. In the example below, ordering by two columns means no extra rows qualify:

PgSQL

The key takeaway: WITH TIES evaluates the full sort key, not just one column. This makes it useful beyond simple pagination — the same semantics can be applied with window functions for more advanced analytical queries.