SQL同日期记录标记最新版本 窗口函数嵌套报错解决

• 原逻辑判断最新记录仅按SRC_START_DTTM降序排序，当多条记录起始日期完全相同时，数据库对同值记录的返回顺序没有确定性，才会出现低版本11092.0003被错标为当前记录的问题。
• 直接在窗口函数的ORDER BY子句中嵌套另一个窗口函数计算版本号，违反SQL语法规则：窗口函数不能嵌套在其他窗口函数的计算逻辑中，因此触发嵌套报错。

不要嵌套窗口函数，先通过子查询/CTE提前计算好版本ID，再基于明确的排序规则标记最新记录即可。推荐用ROW_NUMBER() = 1替代LAG()判断首条记录，逻辑更直观，也不会出现同值顺序不确定的问题。

修正后的完整代码如下：

WITH PreCalculateVersion AS (
    SELECT
        DIM_Record.DWID,    
        CONVERT(float, (DIM_Record.DWID + (ROW_NUMBER() OVER (
            PARTITION BY DIM_Record.DWID 
            ORDER BY DIM_Record.SRC_START_DTTM ASC
            -- 若业务上有同日期下区分版本先后的明确字段（如记录创建时间、自增主键、操作流水号），请补充在此处作为次级排序，保证版本号生成顺序符合预期
        ) * .0001)) - .0001) AS [Version ID],
        DIM_Record.SRC_START_DTTM AS Start_date,
        DIM_Record.DSN,
        DIM_Record.PRJ_TYP_DSID
    FROM Date_Table.DIM_Record_HISTORY DIM_Record
    WHERE DIM_Record.DWID = '11092'
)
SELECT
    pv.[Version ID],
    CONVERT(bit, CASE WHEN ROW_NUMBER() OVER (
        PARTITION BY pv.DWID -- 按DWID分区
        ORDER BY pv.Start_date DESC, pv.[Version ID] DESC -- 先按日期倒序，同日期按版本号倒序，排第一的就是最新
    ) = 1 THEN 1 ELSE 0 END) AS [Current Record],
    pv.Start_date
FROM PreCalculateVersion pv
LEFT OUTER JOIN Date_Table.DIM_Record_TYP typ
    ON pv.DSN = typ.DSN
    AND pv.PRJ_TYP_DSID = typ.DSID

原版本ID计算逻辑中，ROW_NUMBER()仅按SRC_START_DTTM排序，同日期下的版本号分配是随机的。如果业务上有明确的版本先后判定规则，一定要把对应字段补充到版本ID计算的ORDER BY子句中，从根源上避免版本号生成顺序错乱。

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