在SQL中查找最新版本（匹配日期匹配时错误）

Question

我正在尝试按照记录/版本＃来找出最古老的版本和订单。但是，这似乎有效，我遇到了一个错误，其中最新记录的开始日期是相同的（版本11092.0004和11092.0003）。 如何使其将11092.0004视为最新记录？

        SELECT     
                CONVERT(float, (DIM_Record.DWID + (ROW_NUMBER() OVER (PARTITION BY DIM_Record.DWID ORDER BY DIM_Record.SRC_START_DTTM)*.0001)) - .0001) AS [Version ID]
            ,   CONVERT(bit, CASE WHEN LAG(DIM_Record.SRC_START_DTTM) OVER (PARTITION BY DIM_Record.DWID ORDER BY DIM_Record.SRC_START_DTTM DESC) IS NULL THEN 1 ELSE 0 END) AS [Current Record]
            ,    DIM_Record.SRC_START_DTTM as Start_date
        FROM Date_Table.DIM_Record_HISTORY DIM_Record
            LEFT OUTER JOIN Date_Table.DIM_Record_TYP
                ON DIM_Record.DSN = DIM_Record_TYP.DSN
                    AND DIM_Record.PRJ_TYP_DSID = DIM_Record_TYP.DSID
        WHERE DIM_Record.DWID = '11092'

我试图编辑我的代码，以将[版本ID]添加到我的代码中，以获取[当前记录]，

CONVERT(bit, CASE WHEN LAG(DIM_Record.SRC_START_DTTM) OVER (PARTITION BY DIM_Record.DWID ORDER BY (    CONVERT(float, (DIM_Record.DWID + (ROW_NUMBER() OVER (PARTITION BY DIM_Record.DWID ORDER BY DIM_Record.SRC_START_DTTM)*.0001)) - .0001)) DESC) IS NULL THEN 1 ELSE 0 END) AS [Current Record]

但是我会收到以下错误：

窗口函数不能在另一个窗口函数的上下文中使用

我的数据：

版本ID	当前记录	start_date
11092.0003	1	6/2/2022 12:00:00 AM
11092.0004	0	6/2/2022 12:00:00 AM
11092.0002	0	1/1/1/ 31/2022 5:36:46 PM
11092.0001	0	1/31/2022 12:00:00 AM
11092	0	1/3/2022 4:31:44 PM

这里版本ID ID：11092.0004，我希望当前记录读取1。

Answer 1

使用row_number（） = count（*）确定它是否是系列中的最后一个运行号码

CASE WHEN ROW_NUMBER() OVER (PARTITION BY DIM_Record.DWID 
                                 ORDER BY DIM_Record.SRC_START_DTTM)
        = COUNT(*)     OVER (PARTITION BY DIM_Record.DWID)
     THEN 1
     ELSE 0
     END