如何为每个ItemModel筛选出最新的最后修改日期时间？

我从两张表中查询数据，使用的SQL语句如下：

SELECT  H.CostControlID, H.ItemModel, H.effDate,H.LstMdf, D.DTP
FROM PBMHCostControl H
LEFT JOIN PBMDCostControl D
ON H.CostControlID = D.CostControlID

上述查询返回的结果（仅展示ItemModel和LastModifiedDate字段）如下：

但我期望得到的结果是每个ItemModel仅对应最新的最后修改日期时间，具体如下：

请问如何修改SQL语句以实现该需求？

解决方案 #

方法一：仅获取ItemModel和最新修改日期 #

如果只需要ItemModel和对应的最新修改日期，直接用分组加聚合函数即可：

SELECT 
    H.ItemModel,
    MAX(COALESCE(H.LstMdf, D.DTP)) AS LastModifiedDate
FROM PBMHCostControl H
LEFT JOIN PBMDCostControl D
ON H.CostControlID = D.CostControlID
GROUP BY H.ItemModel

说明：COALESCE用来优先取H.LstMdf的值，如果该字段为空则取D.DTP，你可以根据实际业务中LastModifiedDate对应的字段调整，比如确认修改日期只来自H.LstMdf，就直接写MAX(H.LstMdf)。

方法二：保留原查询的所有字段 #

如果需要保留原SQL中的CostControlID、effDate等字段，同时只保留每个ItemModel的最新记录，用窗口函数更合适：

WITH RankedRecords AS (
    SELECT 
        H.CostControlID, 
        H.ItemModel, 
        H.effDate,
        H.LstMdf, 
        D.DTP,
        COALESCE(H.LstMdf, D.DTP) AS LastModifiedDate,
        ROW_NUMBER() OVER (PARTITION BY H.ItemModel ORDER BY COALESCE(H.LstMdf, D.DTP) DESC) AS rn
    FROM PBMHCostControl H
    LEFT JOIN PBMDCostControl D
    ON H.CostControlID = D.CostControlID
)
SELECT 
    CostControlID, 
    ItemModel, 
    effDate,
    LstMdf, 
    DTP,
    LastModifiedDate
FROM RankedRecords
WHERE rn = 1

这个逻辑是给每个ItemModel下的记录按修改日期降序排名，只保留排名第一的那条最新记录，同时保留所有原字段。

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