PostgreSQL equivalent for TOP n WITH TIES: LIMIT "with ties"?

Question

I'm looking for something similar this in SQL Server:

SELECT TOP n WITH TIES FROM tablename

I know about LIMIT in PostgreSQL, but does the equivalent of the above exist? I'm just curious as it would save an extra query each time for me.

If I have a table Numbers with attribute nums: {10, 9, 8, 8, 2}. I want to do something like:

SELECT nums FROM Numbers ORDER BY nums DESC LIMIT *with ties* 3

It should return {10, 9, 8, 8} because it takes the top 3 plus the extra 8 since it ties the other one.

Answer 1

Postgres 13 finally adds WITH TIES . See:

• Get top row(s) with highest value, with ties

---

There is no WITH TIES clause up to PostgreSQL 12, like there is in SQL Server.
In PostgreSQL I would substitute this for TOP n WITH TIES .. ORDER BY <something>:

WITH cte AS (
   SELECT *, rank() OVER (ORDER BY <something>) AS rnk
   FROM   tbl
   )
SELECT *
FROM   cte
WHERE  rnk <= n;

To be clear, rank() is right, dense_rank() would be wrong (return too many rows).
Consider this quote from the SQL Server docs (from the link above):

For example, if expression is set to 5 but 2 additional rows match the values of the ORDER BY columns in row 5, the result set will contain 7 rows.

The job of WITH TIES is to include all peers of the last row in the top n as defined by the ORDER BY clause. rank() gives the exact same result.

To make sure, I tested with SQL Server, here is a live demo.

db<>fiddle here

Faster alternatives for big tables in Postgres 12 or older:

• Equivalent for FETCH FIRST WITH TIES in Postgres 11 with comparable performance