Postgresql 嵌套选择 max(sum())

Question

我的查询:

select table.attribute, count(table.attribute) AS cnt from table
group by table.attribute
order by cnt desc;

输出是这样的:

 attribute | cnt 
-----------+-----
 A         |   2
 B         |   2
 G         |   1
 F         |   1

但我只想要最大值(A 和 B)。

Answer 1

您可以使用单层嵌套来做到这一点:

select attribute, 
       cnt
from (
  select attribute, 
         count(*) AS cnt, 
         max(count(*)) over () as max_cnt
  from t
  group by attribute
) t
where cnt = max_cnt;