如何在SQL Server中按组最高值排序分组，再按组内值降序排列

假设存在如下数据表（表名假设为YourTable）：

需要实现的排序逻辑：

• 按Key分组，先以组内最高Value对分组进行排序；
• 若多个分组的最高Value相同，则按组内次高Value排序；
• 每个分组内部的Value按降序排列。

最终期望得到的结果：

解决方案 #

可以通过窗口函数实现该逻辑，具体SQL语句如下：

SELECT 
    Key,
    Value
FROM (
    SELECT 
        Key,
        Value,
        -- 组内Value降序排名，用于提取次高值
        ROW_NUMBER() OVER (PARTITION BY Key ORDER BY Value DESC) AS rn,
        -- 组内最高Value
        MAX(Value) OVER (PARTITION BY Key) AS group_max,
        -- 组内次高Value（组内不足2个值时为NULL，排序时默认排在非NULL值后）
        MAX(CASE WHEN ROW_NUMBER() OVER (PARTITION BY Key ORDER BY Value DESC) = 2 THEN Value END) OVER (PARTITION BY Key) AS group_second_max
    FROM YourTable
) t
ORDER BY 
    group_max DESC, -- 先按组最高值降序
    group_second_max DESC, -- 组最高值相同时按次高值降序
    Value DESC; -- 组内按Value降序

逻辑说明 #

• 内层查询中：
  • ROW_NUMBER() OVER (PARTITION BY Key ORDER BY Value DESC)：给每个Key分组内的Value按降序分配序号，用于定位次高值。
  • MAX(Value) OVER (PARTITION BY Key)：计算每个分组的最高Value，作为分组排序的第一依据。
  • MAX(CASE WHEN rn=2 THEN Value END) OVER (PARTITION BY Key)：筛选出分组内排名第2的Value（次高值），作为分组排序的第二依据；若分组内只有1个值，次高值为NULL，排序时会自动排在非NULL值之后。
• 外层查询通过ORDER BY依次指定分组排序规则和组内排序规则，最终得到目标结果。

扩展：处理次高值并列的情况 #

如果分组内存在多个并列的次高值，可将ROW_NUMBER()替换为DENSE_RANK()，确保所有并列第二的值都被纳入次高值计算：

SELECT 
    Key,
    Value
FROM (
    SELECT 
        Key,
        Value,
        DENSE_RANK() OVER (PARTITION BY Key ORDER BY Value DESC) AS rnk,
        MAX(Value) OVER (PARTITION BY Key) AS group_max,
        MAX(CASE WHEN DENSE_RANK() OVER (PARTITION BY Key ORDER BY Value DESC) = 2 THEN Value END) OVER (PARTITION BY Key) AS group_second_max
    FROM YourTable
) t
ORDER BY 
    group_max DESC,
    group_second_max DESC,
    Value DESC;

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