Snowflake查询需求：其他列相同时仅返回Fruit列最大值的行

当前输出数据 #

期望输出 #

每个ID分组中，返回Fruit列字典序最大值对应的完整行：

错误尝试分析 #

• 使用ROW_NUMBER() OVER PARTITION未得到正确结果：大概率是未在窗口函数中添加ORDER BY Fruit DESC排序逻辑，导致无法定位到Fruit最大的行。
• SELECT DISTINCT无法实现需求：该关键字仅做去重处理，无法针对分组内的特定列（Fruit）筛选最大值行。
• SELECT MAX(Fruit)报GROUP BY错误：Snowflake要求SELECT中的非聚合列必须出现在GROUP BY子句中，或被聚合函数包裹，直接查询City、Quantity这类非聚合列但未加入GROUP BY会触发报错。

正确解决方案 #

方案一：用ROW_NUMBER()窗口函数筛选 #

通过窗口函数按ID分组，对Fruit降序排序后取第一行：

WITH ranked_data AS (
    SELECT 
        ID,
        City,
        Fruit,
        Quantity,
        -- 按ID分组，Fruit降序排序生成行号
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Fruit DESC) AS row_num
    FROM your_table_name
)
SELECT ID, City, Fruit, Quantity
FROM ranked_data
WHERE row_num = 1;

注意：若同一ID下存在多个Fruit同为最大值的行，ROW_NUMBER()会随机保留一行；若需保留所有这类行，可替换为RANK()或DENSE_RANK()。

方案二：用MAX()聚合+关联原表 #

先分组获取每个ID的最大Fruit，再关联原表取出对应行：

SELECT t.ID, t.City, t.Fruit, t.Quantity
FROM your_table_name t
INNER JOIN (
    -- 按ID分组，获取每个ID的最大Fruit
    SELECT ID, MAX(Fruit) AS max_fruit
    FROM your_table_name
    GROUP BY ID
) m 
ON t.ID = m.ID AND t.Fruit = m.max_fruit;

此方案会返回同一ID下所有Fruit等于最大值的行，适合存在多行匹配的场景。

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