DB2 SQL如何查询分组聚合后所有最大值对应的记录

以下两种方案均无需硬编码最大值、无需提前知晓并列最高的社区数量，数据集更新后可自动适配正确结果：

方案1：窗口函数实现（推荐，DB2全版本支持，执行效率更高） #

先完成各社区犯罪数聚合统计，再通过RANK()窗口函数按犯罪总数降序生成排名，最终筛选排名为1的记录即可，所有并列第一的社区都会被完整返回，不会遗漏。

WITH crime_stats AS (
    SELECT
        community_area_number,
        COUNT(community_area_number) AS total_area_crime
    FROM chicago_crime_data
    GROUP BY community_area_number
)
SELECT community_area_number, total_area_crime
FROM (
    SELECT
        *,
        RANK() OVER (ORDER BY total_area_crime DESC) AS rk
    FROM crime_stats
) ranked_stats
WHERE rk = 1;

注意：不要替换为ROW_NUMBER()函数，该函数会为并列值分配连续不同的排名，会导致并列最高的社区被遗漏；DENSE_RANK()在这里和RANK()效果一致，也可以使用。

方案2：子查询匹配最大值（无窗口函数场景兼容） #

如果是极老版本的DB2不支持窗口函数，可以通过子查询先计算全局单社区最高犯罪数，再通过HAVING子句匹配所有犯罪数等于该最大值的社区：

SELECT
    community_area_number,
    COUNT(community_area_number) AS total_area_crime
FROM chicago_crime_data
GROUP BY community_area_number
HAVING COUNT(community_area_number) = (
    SELECT MAX(total_crime)
    FROM (
        SELECT COUNT(community_area_number) AS total_crime
        FROM chicago_crime_data
        GROUP BY community_area_number
    ) t
);

你之前使用LIMIT 2的硬编码方案缺陷很明确：它只能固定返回前2条结果，一旦数据集更新后最高犯罪数变化、或者并列最高的社区数量不是2，返回结果就会出错，上述两种方案完全规避了这个问题。

内容的提问来源于stack exchange，提问作者Sajjad Ahmed