WITH TIESQuery Techniques
Buy Me A Coffee

An extension to FETCH FIRST / LIMIT that includes additional rows that tie on the last ORDER BY value rather than cutting them off arbitrarily.

A modifier on TOP/LIMIT that includes additional rows that tie with the last returned row, useful for including all top-ranked results when the Nth and N+1st rows are tied. Prevents arbitrary cutoff of equal-ranked items.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✗ Not Supported — WITH TIES is not supported. Workaround: use RANK() in a subquery and filter WHERE rnk <= n.
PostgreSQL ✓ Supported 13 FETCH FIRST n ROWS WITH TIES added in PostgreSQL 13.
SQL Server ✓ Supported 2008 Supported via SELECT TOP n WITH TIES. Requires an ORDER BY clause.
Oracle ✓ Supported 12c Supported in the FETCH FIRST syntax introduced in 12c.
SQLite ✗ Not Supported — WITH TIES is not supported. Use a RANK() window function in a subquery as a workaround.

Details

WITH TIES is handy for leaderboards and ranking queries where arbitrary tie-breaking at the boundary is undesirable. SQL Server's TOP n WITH TIES syntax predates the standard FETCH FIRST form. PostgreSQL added it relatively late (13). MySQL and SQLite both lack it entirely — the portable workaround is consistent across all engines: wrap with RANK() OVER (ORDER BY col) and filter WHERE rnk <= n in an outer query. Note that WITH TIES can return more rows than n — an important consideration for pagination or when a caller assumes a fixed row count.

Standard Syntax

SELECT * FROM t ORDER BY score DESC FETCH FIRST 3 ROWS WITH TIES;

Version Support

MySQL: Not supported PostgreSQL: Since 13 SQL Server: Since 2008 Oracle: Since 12c SQLite: Not supported

Per-Database Syntax & Notes

PostgreSQL

FETCH FIRST n ROWS WITH TIES added in PostgreSQL 13.

SELECT * FROM leaderboard ORDER BY score DESC FETCH FIRST 3 ROWS WITH TIES;

SQL Server

Supported via SELECT TOP n WITH TIES. Requires an ORDER BY clause.

SELECT TOP 3 WITH TIES * FROM leaderboard ORDER BY score DESC;

Oracle

Supported in the FETCH FIRST syntax introduced in 12c.

SELECT * FROM leaderboard ORDER BY score DESC FETCH FIRST 3 ROWS WITH TIES;