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
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
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 2 ROWS WITH TIES OFFSET 3;
|Liebre de Marzo||1300||engineering|
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.