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

PostgreSQL's Equivalent to SQL Server's TOP n WITH TIES

In SQL Server, the TOP n WITH TIES clause allows for retrieving the top n rows from a table, including ties in the result set. PostgreSQL does not have a direct equivalent of this feature. However, there are alternative methods to achieve a similar result.

Alternative Methods:

Option 1: Using CTE and Rank

In PostgreSQL versions up to 12, you can use a common table expression (CTE) with a rank() function to achieve the effect of WITH TIES:

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

For example, with the Numbers table containing {10, 9, 8, 8, 2}, the following query would return {10, 9, 8, 8}:

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

Faster Alternatives for PostgreSQL 12 and Older:

For large tables, the following alternative methods provide faster performance:

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

PostgreSQL 13 and Up:

PostgreSQL 13 introduces the WITH TIES clause, providing a native implementation of this functionality:

SELECT nums FROM Numbers ORDER BY nums DESC LIMIT 3 WITH TIES;

This query would correctly return {10, 9, 8, 8}.

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