11

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 attribute nums: {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 extra 8 since it ties the other one.

Andrew
  • 2,519
  • 6
  • 29
  • 46

3 Answers3

20

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    Why this complicated? The rank() function will do exactly what is required? –  Mar 09 '12 at 08:01
  • @a_horse_with_no_name: You are absolutely right (as was I, at first) My second considerations were uncalled for. – Erwin Brandstetter Mar 09 '12 at 08:06
  • @Erwin: that's nice(http://data.stackexchange.com/stackoverflow/query/63525/top-n-with-ties), 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: http://stackoverflow.com/questions/2611822/distinct-with-count-and-sql-server-2005/2612456#2612456 – Michael Buen Mar 09 '12 at 08:49
  • @MichaelBuen: I am sure there are situations out there where you want the results from `dense_rank()`. But the question here refers to `top n with ties`, which is an obvious reference to SQL server syntax. Consequently, `rank()` is the right one here. – Erwin Brandstetter Mar 09 '12 at 08:58
  • Anyway, we will know from the OP, it depends on his problem's case – Michael Buen Mar 09 '12 at 09:06
  • If the top two tie, then there will only be one more so 10,10,9 is right. – Michael Brown Jan 16 '19 at 15:06
2

PostgreSQL already supports OFFEST FETCH clause and starting from version 13 it will support FETCH FIRST WITH TIES:

SELECT

[ 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;

db<>fiddle demo

Community
  • 1
  • 1
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
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
Michael Buen
  • 38,643
  • 9
  • 94
  • 118