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

by

Postgres 13 finally adds WITH TIES . See:

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:

More Related Contents:

  1. PostgreSQL unnest() with element number
  2. Dynamic alternative to pivot with CASE and GROUP BY
  3. Best way to get result count before LIMIT was applied
  4. PostgreSQL: running count of rows for a query ‘by minute’
  5. Calculating Cumulative Sum in PostgreSQL
  6. GROUP BY and aggregate sequential numeric values
  7. Get the distinct sum of a joined table column
  8. Get top row(s) with highest value, with ties
  9. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  10. Window Functions or Common Table Expressions: count previous rows within range
  11. Postgres window function and group by exception
  12. Referencing current row in FILTER clause of window function
  13. How to group timestamps into islands (based on arbitrary gap)?
  14. How to use a ring data structure in window functions
  15. Conditional lead/lag function PostgreSQL?
  16. How do I (or can I) SELECT DISTINCT on multiple columns?
  17. How to speed up insertion performance in PostgreSQL
  18. PostgreSQL GROUP BY different from MySQL?
  19. How can I import a JSON file into PostgreSQL?
  20. Is there a way to define a named constant in a PostgreSQL query?
  21. Postgres unique constraint vs index
  22. Perform this hours of operation query in PostgreSQL
  23. Getting date list in a range in PostgreSQL
  24. Postgres Dynamic Query Function
  25. How do I delete a fixed number of rows with sorting in PostgreSQL?
  26. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  27. How to use an ALIAS in a PostgreSQL ORDER BY clause?
  28. PostgreSQL does not accept column alias in WHERE clause
  29. pseudo_encrypt() function in plpgsql that takes bigint
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment