PostgreSQL相当于TOP n WITH TIES：LIMIT“with ties”？

I'm looking for something similar this in SQL Server:

我在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.

我知道PostgreSQL中的LIMIT，但是上面存在的相同吗？我很好奇，因为它每次都会为我节省额外的查询。

If I have a table Numbers with attribute nums: {10, 9, 8, 8, 2}. I want to do something like:

如果我有一个带有属性nums的表号：{10,9,8,8,2}。我想做的事情如下：

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.

它应该返回{10,9,8,8}，因为它需要前三个加上额外的8，因为它与另一个相关。

2 个解决方案

#1

There is no WITH TIES clause in PostgreSQL like there is in SQL Server.
In PostgreSQL I would substitute this for TOP n WITH TIES .. ORDER BY <something>:

PostgreSQL中没有像在SQL Server中那样的WITH TIES子句。在PostgreSQL中，我会将其替换为TOP n WITH TIES .. ORDER BY ：

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):

要清楚，rank（）是正确的，dense_rank（）是错误的（返回太多行）。请考虑SQL Server文档中的引用（来自上面的链接）：

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.

例如，如果expression设置为5但是另外两行与第5行中ORDER BY列的值匹配，则结果集将包含7行。

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.

WITH TIES的工作是包括ORDER BY子句定义的前n中最后一行的所有对等体。 rank（）给出完全相同的结果。

To make sure, I tested with SQL server, here is a live demo.
And here is a more convenient SQLfiddle.

为了确保，我使用SQL服务器进行了测试，这是一个现场演示。这是一个更方便的SQLfiddle。

#2

Try this:

尝试这个：

Output: 10, 9, 8, 8

输出：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

产量：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

#1