How to Simulate SQL Server's TOP n WITH TIES in PostgreSQL?

PostgreSQL Equivalent for TOP n WITH TIES: LIMIT "with ties"

PostgreSQL does not offer a direct equivalent to SQL Server's TOP n WITH TIES clause. The LIMIT clause in PostgreSQL is used to specify the number of rows to return, but it does not include ties.

Alternative Approach Using Common Table Expressions (CTEs)

In PostgreSQL versions up to 12, an alternative approach involves using a Common Table Expression (CTE) with a ranking function to simulate the WITH TIES behavior:

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

Here, rank() assigns a ranking to each row based on the ORDER BY clause. The <= comparison in the WHERE clause ensures that all rows with a rank less than or equal to n are selected, including any ties.

Faster Alternatives for Large Tables in PostgreSQL 12 or Older

For large tables, an alternative approach that is faster than the CTE method is provided here:

[Equivalent for FETCH FIRST WITH TIES in Postgres 11 with comparable performance](https://www.db-fiddle.com/f/h6i8rtyv234ucf653j9k8x46q/0)

PostgreSQL 13 and Beyond

As of PostgreSQL 13, the WITH TIES clause has been officially added. This clause can be used directly with the LIMIT clause to achieve the desired behavior:

SELECT *
FROM   tablename
ORDER BY nums DESC
LIMIT 3 WITH TIES;

This will return {10, 9, 8, 8}, as desired, by taking the top 3 rows and including the extra 8 row that ties with the third row.

The above is the detailed content of How to Simulate SQL Server's TOP n WITH TIES in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!