PostgreSQL equivalent for TOP n WITH TIES: LIMIT "with ties"?
Solution 1
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:
Solution 2
Try this:
Output: 10, 9, 8, 8
with numbers (nums) as (
values (10), (9), (8), (8), (2)
)
SELECT nums FROM Numbers
WHERE nums in (SELECT DISTINCT nums FROM Numbers ORDER BY nums DESC LIMIT 3)
ORDER BY nums DESC
Output: 10,10,9,8,8
with numbers (nums) as (
values (10), (9), (8), (8), (2), (10)
)
SELECT nums FROM Numbers
WHERE nums in (SELECT DISTINCT nums FROM Numbers ORDER BY nums DESC LIMIT 3)
ORDER BY nums DESC
Solution 3
PostgreSQL already supports OFFEST FETCH
clause and starting from version 13 it will support FETCH FIRST WITH TIES
:
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
The WITH TIES option is used to return any additional rows that tie for the last place in the result set according to the ORDER BY clause; ORDER BY is mandatory in this case.
Query:
SELECT nums
FROM Numbers
ORDER BY nums DESC
FETCH NEXT 3 ROWS WITH TIES;
Andrew
Updated on June 27, 2022Comments
-
Andrew almost 2 years
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 attributenums
:{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 extra8
since it ties the other one. -
a_horse_with_no_name about 12 yearsWhy this complicated? The rank() function will do exactly what is required?
-
Erwin Brandstetter about 12 years@a_horse_with_no_name: You are absolutely right (as was I, at first) My second considerations were uncalled for.
-
Alexandre Leites about 12 years@Erwin: that's nice(data.stackexchange.com/stackoverflow/query/63525/…), I didn't know stackoverflow has a built-in module to run queries :-) Though the output of 10,10,9 for 10,9,8,8,2,10 is incorrect if you need to get the top 3(10,9,8) and list all those 10,9,8; which should be: 10,10,9,8,8.
WITH TIES
might be incorrect for some given problem case: stackoverflow.com/questions/2611822/… -
Erwin Brandstetter about 12 years@MichaelBuen: I am sure there are situations out there where you want the results from
dense_rank()
. But the question here refers totop n with ties
, which is an obvious reference to SQL server syntax. Consequently,rank()
is the right one here. -
Alexandre Leites about 12 yearsAnyway, we will know from the OP, it depends on his problem's case
-
Michael Brown over 5 yearsIf the top two tie, then there will only be one more so 10,10,9 is right.