SQL Server双条件排名查询结果不符预期的修正请求

各位好，我现在在SQL Server里处理一个排名需求时遇到了问题，想请大家帮忙看看怎么调整查询语句。我的需求是：

• 按ID分组对记录进行排名
• 优先依据Date Completed字段（由两个表的日期字段通过COALESCE转换而来）的最新值排序
• 对于同一Date Completed的记录，要让response字段不为空的记录排在空值记录前面

当前查询语句 #

WITH RankedDates AS (
    SELECT 
        DE.[ID],
        DE.[response],
        MAX(COALESCE(TRY_CONVERT(DATE, DE.[closed_on], 103), TRY_CONVERT(DATE, OE.[closed_at], 103))) AS [Date Completed],
        ROW_NUMBER() OVER (
            PARTITION BY DE.[ID] 
            ORDER BY 
                MAX(COALESCE(TRY_CONVERT(DATE, DE.[closed_on], 103), TRY_CONVERT(DATE, OE.[closed_at], 103))) DESC,
                CASE WHEN DE.[response] IS NULL THEN 1 ELSE 0 END DESC
        ) AS Rank
    FROM [Live].[Import_D_2025-08-13] DE
    LEFT OUTER JOIN [Live].[Import_O_2025-08-13] OE 
        ON DE.[ID] = OE.[ID]
    WHERE DE.[ID] IN ('100')
    GROUP BY DE.[ID], DE.[response]
)
SELECT * 
FROM RankedDates 
ORDER BY [Date Completed] ASC, Rank;

当前输出结果 #

预期输出结果 #

问题分析与修正方案 #

我排查后发现问题出在ROW_NUMBER()函数的排序逻辑上：当前的CASE表达式在response为空时返回1，非空时返回0，再加上DESC排序，反而让空值记录排在了非空记录前面，完全和需求相反。

只需要把CASE部分的排序方向从DESC改成ASC，就能让非空的response记录优先获得更小的排名值（排在前面）：

WITH RankedDates AS (
    SELECT 
        DE.[ID],
        DE.[response],
        MAX(COALESCE(TRY_CONVERT(DATE, DE.[closed_on], 103), TRY_CONVERT(DATE, OE.[closed_at], 103))) AS [Date Completed],
        ROW_NUMBER() OVER (
            PARTITION BY DE.[ID] 
            ORDER BY 
                MAX(COALESCE(TRY_CONVERT(DATE, DE.[closed_on], 103), TRY_CONVERT(DATE, OE.[closed_at], 103))) DESC,
                CASE WHEN DE.[response] IS NULL THEN 1 ELSE 0 END ASC -- 此处将DESC改为ASC
        ) AS Rank
    FROM [Live].[Import_D_2025-08-13] DE
    LEFT OUTER JOIN [Live].[Import_O_2025-08-13] OE 
        ON DE.[ID] = OE.[ID]
    WHERE DE.[ID] IN ('100')
    GROUP BY DE.[ID], DE.[response]
)
SELECT * 
FROM RankedDates 
ORDER BY [Date Completed] ASC, Rank;

调整后，同一Date Completed的记录中，response非空的会排在前面，完全符合预期的输出结果。

内容来源于stack exchange