SQL Server supports a TOP (N) WITH TIES clause which can be used to return the top N rows from a query along with any additional rows that have the same sort value as the Nth row. It's handy functionality that can come in use in many situations. In this post, we'll take a look at how you can implement the equivalent functionality in PostgreSQL (you can find the full source code to reproduce this example on GitHub here for SQL Server and here for PostgreSQL).

How SQL Server's WITH TIES works

Starting off with a table that holds movies and their lengths in minutes.

select *
from movies
order by length desc;

movie_id|title                    |length|
--------|-------------------------|------|
       7|Rocketman                |   150|
      10|The Secret Life of Pets 2|   140|
       5|Hellboy                  |   140|
       6|Anna                     |   140|
       3|Brightburn               |   120|
       4|The Intruder             |   120|
       9|Men in Black             |   110|
       1|Adventure Time           |   100|
       2|Aladdin                  |   100|
       8|Midsommar                |   100|

Imagine now we wanted to write a query to return the three longest movies. We would have a a strong case for returning four though! There's one movie that's 150 minutes and then three movies that are each 140 minutes. There's no good reason we should have to pick only two out of the three movies that are 140 minutes (and how would we choose which two of the three to pick? Randomly?). In SQL Server, WITH TIES lets us handle this - returning the top three movies by length and then any other movies that also have the same length as the third movie.

select top (3) with ties title, length
from movies
order by length desc;

title                    |length|
-------------------------|------|
Rocketman                |   150|
The Secret Life of Pets 2|   140|
Hellboy                  |   140|
Anna                     |   140|

Implementing WITH TIES in PostgreSQL

This is specific to SQL Server though. How can we accomplish the same thing in PostgreSQL? With a table subquery and the rank() window function!

select title, length
from 
  (select
     title,
     length,
     rank() over (order by length desc)
   from movies) as ranked_movies
where rank <= 3
order by length desc;

The rank window function works over a window of movies ordered by length, assigning each a rank, with the same rank used for matching values. By using this within a table subquery (or we could use a Common Table Expression), we're now able to filter on that rank in the WHERE clause to only return movies with rank less than or equal to 3.

As with all my blog posts, you can find the full source code on GitHub here for SQL Server and here for PostgreSQL.