PostgreSQL 13: LIMIT … WITH TIES

One of the new features in PostgreSQL 13 is the SQL-standard WITH TIES clause to use with LIMIT — or, as the standard calls that, FETCH FIRST n ROWS. Thanks are due to Surafel Temesgen as initial patch author; Tomas Vondra and yours truly for some additional code fixes; and reviewers Andrew Gierth and Erik Rijkers. You can peruse the commit message.

Ties are very frequently when ranking things; for instance, in a caucus race you could have many ties, and for sure you don’t want to deprive participants of their prizes! What WITH TIES does is pretty simple: it adds any following row or rows to your result set, if they rank equal to the last row returned per the LIMIT clause, according to the ORDER BY clause.

If you want just the two employees with the highest salary, you may do this:

SELECT * FROM employees
ORDER BY salary DESC LIMIT 2;

So are you itching to know the salary of the next person? What if she matches Oruga, and was just left out by pure chance or bad luck? That can happen, as you well know; and fortunately, WITH TIES is now there to save the day. (Note that, in reality, we do not handle WITH TIES in the LIMIT clause as such.  You have to use the FETCH FIRST syntax, which is the standards-mandated one, in order to be able to use WITH TIES.)

SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 2 ROWS WITH TIES;

There! White Rabbit had to be listed, and now he is.

A couple notes before you go too mad. LIMIT (or more precisely FETCH FIRST) no longer promises to return exactly the number of rows you specify. You could get two or twenty additional rows, or 100x as many rows as you asked for. Among other things this means that you need to keep track of how many rows you’ve seen thus far, if you’re paginating results. In the above, you got three rows, so for the next page you skip that many by adding the right OFFSET clause:

SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 2 ROWS WITH TIES
OFFSET 3;

We again got three rather than just two we asked for. So for the next page you’d have to skip six. And so on. Be sure to have enough thimbles for everybody.

The other thing to keep in mind is that you must make sure to only use the ORDER BY clause that suits the WITH TIES clause; if you wanted, say, to have the rows of the same salary ordered by name, you’d have to use a subquery. Otherwise, the distinction in names would solve the tie on salary, so the next row would not be included. For example:

SELECT * FROM (
       SELECT * FROM employees
       ORDER BY salary DESC
       FETCH FIRST 2 ROWS WITH TIES) AS subq
ORDER BY salary DESC, name;

This feature is there to help you show all rows that are of the same value — it lets you not discriminate against some rows of equal worth based solely on physical location within the table.

Happy paginating!