SQL分组查询疑难：添加多列后出现重复行，如何获取最新记录？

原问题与SQL语句 #

当前有如下SQL查询：

SELECT H1.INCIDENT_NUMBER,
    H1.HISTORY_START_DATE,
    H1.ASSIGNED_GROUP, 
    H1.STATUS
FROM INCIDENT_HISTORY_PUBLIC as H1
WHERE H1.INCIDENT_NUMBER IN (
   SELECT INCIDENT_NUMBER
   FROM INCIDENT_HISTORY_PUBLIC
   WHERE ASSIGNED_GROUP LIKE ' DS$_%' ESCAPE '$'
)
ORDER BY H1.INCIDENT_NUMBER

该查询返回部分结果，需求是为每个INCIDENT_NUMBER获取MAX(HISTORY_START_DATE)对应的完整记录。尝试使用GROUP BY，但加入ASSIGNED_GROUP和STATUS后，每个工单编号又出现多行，期望每个工单仅显示最新时间对应的行。

解决方案 #

GROUP BY无法直接满足需求的原因是：当你按INCIDENT_NUMBER, ASSIGNED_GROUP, STATUS分组时，只要这三个字段的组合存在差异，就会生成独立分组，导致同一工单出现多行记录。你可以通过以下两种方式实现需求：

方法1：子查询关联 #

先筛选出符合条件的工单，并计算每个工单的最新时间，再关联原表获取对应记录：

SELECT H1.INCIDENT_NUMBER,
       H1.HISTORY_START_DATE,
       H1.ASSIGNED_GROUP, 
       H1.STATUS
FROM INCIDENT_HISTORY_PUBLIC as H1
INNER JOIN (
    SELECT INCIDENT_NUMBER, MAX(HISTORY_START_DATE) AS LATEST_DATE
    FROM INCIDENT_HISTORY_PUBLIC
    WHERE ASSIGNED_GROUP LIKE ' DS$_%' ESCAPE '$'
    GROUP BY INCIDENT_NUMBER
) AS H2 
    ON H1.INCIDENT_NUMBER = H2.INCIDENT_NUMBER 
    AND H1.HISTORY_START_DATE = H2.LATEST_DATE
ORDER BY H1.INCIDENT_NUMBER;

方法2：窗口函数（更简洁） #

使用ROW_NUMBER()窗口函数为每个工单的记录按时间倒序排名，取排名第1的记录（即最新行）：

WITH ranked_incidents AS (
    SELECT 
        INCIDENT_NUMBER,
        HISTORY_START_DATE,
        ASSIGNED_GROUP,
        STATUS,
        ROW_NUMBER() OVER (PARTITION BY INCIDENT_NUMBER ORDER BY HISTORY_START_DATE DESC) AS rn
    FROM INCIDENT_HISTORY_PUBLIC
    WHERE ASSIGNED_GROUP LIKE ' DS$_%' ESCAPE '$'
)
SELECT INCIDENT_NUMBER, HISTORY_START_DATE, ASSIGNED_GROUP, STATUS
FROM ranked_incidents
WHERE rn = 1
ORDER BY INCIDENT_NUMBER;

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