Retrieving the top N records with ties on Postgres

Gabriel Sobrinho – 28 Sep 2023

A customer asked for a top absentees report, and we immediately created a query, but an unexpected behavior happened.

An emulation of the real database, of course!

But there’s an issue with that report that is not entirely obvious:

Why would Richard Doe be considered a top absentee but not Robert Doe with, the same number of absences?

That’s called a tie: Richard Doe and Robert Doe have the same number of absences.

In other words, we might have more than 3 rows to describe the top 3 absentees.

For that scenario, we have a FETCH FIRST … ROWS WITH TIES clause on Postgres 13+:

4 rows to describe top 3 absentees.

If there are no ties, it will behave pretty much the same as LIMIT.

Now we are good to deploy that to production!

Subscribe to receive our posts first hand!