在SQL Server中如何获取每组内differenz值最小的首行？

你需要对每个aeVpid+aeEreignisDat的组合，筛选出differenz最小的记录；如果有多个记录differenz相同，取beStichtagDat最大的那条。用ROW_NUMBER()窗口函数可以精准实现这个需求，核心是正确设置分区和排序规则：

WITH ranked_records AS (
    SELECT 
        ae.vpid AS aeVpid,
        ae.EreignisDat AS aeEreignisDat, 
        be.StichtagDat AS beStichtagDat,
        ABS(DATEDIFF(day, ae.EreignisDat, be.StichtagDat)) AS differenz,
        -- 按aeVpid和aeEreignisDat分组，先按differenz升序，再按beStichtagDat降序排名
        ROW_NUMBER() OVER (
            PARTITION BY ae.vpid, ae.EreignisDat
            ORDER BY ABS(DATEDIFF(day, ae.EreignisDat, be.StichtagDat)) ASC, be.StichtagDat DESC
        ) AS rn
    FROM 
        AEGE AS ae 
    LEFT OUTER JOIN
        BEST AS be ON ae.VPID = be.VPID
    WHERE 
        ae.EreignisDat <> be.StichtagDat
)
SELECT aeVpid, aeEreignisDat, beStichtagDat, differenz
FROM ranked_records
WHERE rn = 1;

关键说明： #

• PARTITION BY ae.vpid, ae.EreignisDat：将数据按你的分组维度（每个AEGE表的记录）拆分，确保每组内单独计算排名
• ORDER BY ... ASC, be.StichtagDat DESC：先按differenz从小到大排序，保证最小的天数差排在前面；如果差相同，取beStichtagDat最大的记录，和你原查询的排序逻辑一致
• 最后筛选rn=1的记录，就是每组内符合要求的首行

如果你之前用窗口函数失败，大概率是分区键没包含ae.EreignisDat，或者排序规则没匹配需求。

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