如何获取按ID降序排序后去重日期的倒数第二条记录

需求说明 #

需先按ID降序排序数据表，再对ForecastDate字段去重，最终获取去重后日期列表中的第二新记录（对应原表id=2的2024-01-03）。尝试双CTE实现时，因CTE无法直接使用ORDER BY、且不想用TOP关键字，现有查询返回错误结果2023-12-05，求正确SQL写法。

测试表结构及数据 #

create table mytable (id int, ForecastDate date);
insert into mytable values
(1,'2023-12-05'),(2,'2024-01-03'),(3,'2024-04-01'),(4,'2024-04-01'),(5,'2024-04-01');

尝试的错误CTE代码 #

WITH DistinctForecastDate AS (
    SELECT DISTINCT
          mytable.ForecastDate AS ForecastDate
    FROM
        mytable
   ORDER BY   mytable.id DESC
)
, RowNumberDate AS (
    SELECT DistinctForecastDate.ForecastDate AS ForecastDate
        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber
    FROM DistinctForecastDate
)
SELECT RowNumberDate.DeliveryDate
FROM RowNumberDate 
WHERE RowNumberDate.RowNumber = 2

错误原因分析 #

1. 第一个CTE中的ORDER BY mytable.id DESC无效：DISTINCT后的ORDER BY仅能作用于选中的字段，这里排序字段id不在SELECT列表中，无法保证按ID降序的顺序返回去重后的日期。
2. 第二个CTE中ORDER BY (SELECT NULL)会导致行号分配顺序完全不确定，无法得到预期的排序结果。

正确SQL写法 #

写法一：通过窗口函数标记首次出现记录 #

WITH RankedDates AS (
    SELECT 
        ForecastDate,
        -- 按ID降序，为每个日期标记首次出现的行
        ROW_NUMBER() OVER (PARTITION BY ForecastDate ORDER BY id DESC) AS rn
    FROM mytable
),
DistinctOrderedDates AS (
    SELECT 
        ForecastDate,
        -- 按每个日期对应的最大ID降序，为去重后的日期编号
        ROW_NUMBER() OVER (ORDER BY (SELECT MAX(id) FROM mytable t WHERE t.ForecastDate = rd.ForecastDate) DESC) AS row_num
    FROM RankedDates rd
    WHERE rn = 1 -- 保留每个日期在ID降序中首次出现的记录
)
SELECT ForecastDate
FROM DistinctOrderedDates
WHERE row_num = 2;

写法二：分组取最大ID后排序编号 #

WITH DistinctDates AS (
    SELECT 
        ForecastDate,
        MAX(id) AS max_id -- 每个日期对应的最大ID，对应ID降序中最先出现的记录
    FROM mytable
    GROUP BY ForecastDate
),
OrderedDates AS (
    SELECT 
        ForecastDate,
        ROW_NUMBER() OVER (ORDER BY max_id DESC) AS row_num
    FROM DistinctDates
)
SELECT ForecastDate
FROM OrderedDates
WHERE row_num = 2;

两种写法均能正确返回目标结果2024-01-03，且未使用TOP关键字，符合需求。

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