Does PostgreSQL Support `TOP n WITH TIES` Functionality?

LIMIT with Ties: Has PostgreSQL Implemented It?

In SQL Server, the syntax TOP n WITH TIES retrieves the first n rows along with any tied rows. However, PostgreSQL users may be wondering if there's an equivalent syntax.

PostgreSQL's Solution: WITH TIES (PostgreSQL 13)

Starting with PostgreSQL 13, the WITH TIES clause was introduced. This clause behaves similarly to its SQL Server counterpart, fetching the top n rows plus any tied rows.

Alternative for PostgreSQL 12 and Older

Prior to PostgreSQL 13, there was no direct equivalent of TOP n WITH TIES. One workaround is to execute the following query:

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

Here, rank() assigns each row a rank, and rows tied for the last rank n are included.

Example

Consider a table Numbers with values {10, 9, 8, 8, 2}. The following query would return {10, 9, 8, 8} using the alternative method for PostgreSQL 12 and older:

WITH cte AS (
   SELECT nums, rank() OVER (ORDER BY nums DESC) AS rnk
   FROM   Numbers
   )
SELECT *
FROM   cte
WHERE  rnk <= 3;

Note: The rank() function used in this alternative is different from dense_rank(), as the latter would produce incorrect results by returning too many rows.

The above is the detailed content of Does PostgreSQL Support `TOP n WITH TIES` Functionality?. For more information, please follow other related articles on the PHP Chinese website!