时间戳

首页 > 解决方案 > 显示列中值的最小数量

sql - 显示列中值的最小数量

问题描述

我使用了一个 select 语句来获取俱乐部列表的计数：

SELECT club,COUNT(club) as mycount FROM member GROUP BY club


   CLUB            COUNT      
--------------- -----------

UCLA  Club                 5
George Club                11

我需要显示计数表的最小数量：

    CLUB            COUNT
--------------- -----------
UCLA Club                  5

我使用了这个语句，但它只显示最小计数为 1 列我需要表格看起来像上面的表格我怎样才能获得俱乐部的名称：

SELECT MIN(mycount) as Count FROM (SELECT club, COUNT(club) mycount FROM member group by club)

我试着做：

SELECT club, MIN(mycount) as Count FROM (SELECT club, COUNT(club) mycount FROM yrb_member group by club)

但是当我在选择后放置俱乐部时它给了我一个错误

标签： sqldb2

解决方案

使用order by和fetch first：

SELECT club, COUNT(club) as mycount
FROM member
GROUP BY club
ORDER BY COUNT(club)
FETCH FIRST 1 ROWS WITH TIES;

注意：当有平局时，这将返回多行。

编辑：

如果您需要处理领带，请使用RANK()：

SELECT club, mycount
FROM (SELECT club, COUNT(club) as mycount,
             RANK() OVER (ORDER BY COUNT(club) DESC) as seqnum
      FROM member
      GROUP BY club
     ) m
WHERE seqnum = 1;

推荐阅读