时间戳相同时，如何获取Serial_No对应的最新SQL数据行？

原始数据 #

多个Serial_No存在多条记录时间戳（Update_Date）完全相同的情况，导致无法获取带有正确Active状态的最新行，现以Serial_No=1Q2171213为例求助。

已尝试的SQL语句 #

语句1 #

SELECT TOP 1
    t1.Change_Key, t1.Serial_No, t1.Part_Key, t1.Quantity,
    t1.Tare_Weight, t1.Gross_Weight, t1.Net_Weight,
    t1.Update_Date, t1.Last_Action, t1.Active
FROM 
    Part_v_Container_Change2 AS T1
INNER JOIN
    (SELECT 
         Serial_No, 
         MAX(Update_Date) AS Update_Date, 
         MAX(Change_Key) AS Change_Key 
     FROM 
         Part_v_Container_Change2 
     GROUP BY Serial_No) AS T2 ON T1.Serial_No = T2.Serial_No 
                               AND T1.Update_Date = T2.Update_Date
WHERE
    t1.Serial_No = '1Q2171213'
ORDER BY 
    1

语句2 #

SELECT
    Change_Key, Serial_No, Part_Key, Quantity,
    Tare_Weight, Gross_Weight, Net_Weight,
    Update_Date, Last_Action, Active
FROM
    (SELECT
         *,
         ROW_NUMBER() OVER(PARTITION BY Serial_No ORDER BY Update_Date DESC) AS rn
     FROM
         Part_v_Container_Change2 cc1) x
WHERE
    rn = 1
    AND Serial_No = '1Q2171213' 
ORDER BY
    1

当前错误结果 #

期望结果 #

解决方案 #

问题根源是时间戳相同时，缺少精准的排序依据。从数据可见Change_Key是递增的，最新操作对应更大的Change_Key，因此需在排序逻辑中加入Change_Key DESC以锁定最新记录。

修改后的ROW_NUMBER版本SQL #

SELECT
    Change_Key, Serial_No, Part_Key, Quantity,
    Tare_Weight, Gross_Weight, Net_Weight,
    Update_Date, Last_Action, Active
FROM
    (SELECT
         *,
         ROW_NUMBER() OVER(PARTITION BY Serial_No ORDER BY Update_Date DESC, Change_Key DESC) AS rn
     FROM
         Part_v_Container_Change2 cc1) x
WHERE
    rn = 1
    AND Serial_No = '1Q2171213' 
ORDER BY
    Change_Key

修改后的JOIN版本SQL #

SELECT TOP 1
    t1.Change_Key, t1.Serial_No, t1.Part_Key, t1.Quantity,
    t1.Tare_Weight, t1.Gross_Weight, t1.Net_Weight,
    t1.Update_Date, t1.Last_Action, t1.Active
FROM 
    Part_v_Container_Change2 AS T1
INNER JOIN
    (SELECT 
         Serial_No, 
         MAX(Update_Date) AS Update_Date, 
         MAX(Change_Key) AS Change_Key 
     FROM 
         Part_v_Container_Change2 
     GROUP BY Serial_No) AS T2 ON T1.Serial_No = T2.Serial_No 
                               AND T1.Change_Key = T2.Change_Key
WHERE
    t1.Serial_No = '1Q2171213'
ORDER BY 
    Change_Key

说明：

• ROW_NUMBER方法：在排序规则中增加Change_Key DESC，确保时间戳相同时，按Change_Key降序排列，最大的Change_Key对应记录会被标记为rn=1。
• JOIN方法：修正关联条件为T1.Change_Key = T2.Change_Key，利用子查询中已获取的每个Serial_No对应的最大Change_Key，直接关联定位最新记录。

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