SQL Server中筛选各ConsentTemplateIdentifier的最高版本行

原表数据 #

预期结果 #

原查询错误分析 #

你的原查询报错是因为SQL Server的GROUP BY规则：SELECT列表中的非聚合列必须全部包含在GROUP BY子句中，或者被聚合函数包裹。你只按ConsentTemplateIdentifier（注：表实际列是ConsentIdentifier，应为笔误）分组，但ID、ConsentTitle等列既不在GROUP BY里，也没有聚合处理，因此触发错误。同时DISTINCT在这里无意义，GROUP BY已经会对分组结果去重。

正确解法 #

方法1：窗口函数ROW_NUMBER()（推荐） #

这是简洁高效的方案，通过窗口函数对每个标识分组内的行按版本降序排名，取每组第一行：

WITH RankedConsents AS (
    SELECT 
        ID,
        ConsentTitle,
        ConsentIdentifier,
        Version,
        DisplayOrder,
        ROW_NUMBER() OVER (PARTITION BY ConsentIdentifier ORDER BY Version DESC) AS RowRank
    FROM FooDocs
)
SELECT 
    ID,
    ConsentTitle,
    ConsentIdentifier,
    Version,
    DisplayOrder
FROM RankedConsents
WHERE RowRank = 1
ORDER BY DisplayOrder;

• PARTITION BY ConsentIdentifier：按标识分组
• ORDER BY Version DESC：让每组内版本最高的行排第1位
• 筛选RowRank=1得到每个标识的最高版本行，最后按DisplayOrder排序

如果存在同一标识下多个行版本相同且均为最高版本的情况，需要保留所有这类行，可将ROW_NUMBER()替换为RANK()。

方法2：子查询关联法 #

先查询每个标识的最高版本值，再关联原表获取对应完整行：

SELECT 
    f.ID,
    f.ConsentTitle,
    f.ConsentIdentifier,
    f.Version,
    f.DisplayOrder
FROM FooDocs f
INNER JOIN (
    SELECT ConsentIdentifier, MAX(Version) AS MaxVersion
    FROM FooDocs
    GROUP BY ConsentIdentifier
) mv ON f.ConsentIdentifier = mv.ConsentIdentifier AND f.Version = mv.MaxVersion
ORDER BY f.DisplayOrder;

子查询先得到每个标识的最高版本，再通过标识和版本匹配原表，提取对应完整行后排序。

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