如何修改SQL查询获取每组最高/次高日期并按PKID打破平局

我想基于现有SQL查询修改，实现以下需求：按FileNumber分组，获取每组最高和次高FilingDate；当FilingDate重复时，用最大的PKID打破平局；同时返回对应记录的PKID字段。

原查询仅能获取分组的最大、次大日期，无法适配带主键平局处理和返回主键的需求。

数据表（XYZQueue） #

FileNumber  FilingDate   PKID
  1          11/9/2023    1
  1          10/9/2023    2
  1          10/9/2023    3
  2          8/30/2023    4
  2          7/30/2023    5
  3          9/15/2023    6
  3          9/1/2023     7
  3          8/31/2023    8
  4          4/5/2023     9
  5          2/10/2023    10
  5          2/10/2023    11
  5          2/1/2023     12

期望结果 #

FileNumber  HighestFilingDate  HighestFilingDatePKID  SecondHighestFilingDate  SecondHighestFilingDatePKID
  1          11/9/2023                    1               10/9/2023                3
  2          8/30/2023                    4               7/30/2023                5
  3          9/15/2023                    6               9/1/2023                 7
  4          4/5/2023                     9
  5          2/10/2023                    11              2/1/2023                 12

使用窗口函数ROW_NUMBER()实现需求，先按分组规则排序并标记排名，再通过条件聚合将结果转换为一行展示。

SQL查询语句 #

SELECT
    FileNumber,
    MAX(CASE WHEN rn = 1 THEN FilingDate END) AS HighestFilingDate,
    MAX(CASE WHEN rn = 1 THEN PKID END) AS HighestFilingDatePKID,
    MAX(CASE WHEN rn = 2 THEN FilingDate END) AS SecondHighestFilingDate,
    MAX(CASE WHEN rn = 2 THEN PKID END) AS SecondHighestFilingDatePKID
FROM (
    SELECT
        FileNumber,
        FilingDate,
        PKID,
        ROW_NUMBER() OVER (
            PARTITION BY FileNumber 
            ORDER BY FilingDate DESC, PKID DESC
        ) AS rn
    FROM XYZQueue
) ranked
GROUP BY FileNumber
ORDER BY FileNumber;

逻辑说明 #

1. 子查询ranked：对每个FileNumber分组内的记录排序，优先按FilingDate降序（最新日期在前），日期相同时按PKID降序（最大主键在前）。排名rn=1为每组的最高记录，rn=2为次高记录。
2. 外层条件聚合：将每个分组的第1、2名记录的日期和PKID分别提取，转换为一行展示；无次高记录的分组（如FileNumber=4），对应次高字段将显示为NULL。

内容的提问来源于stack exchange，提问作者J. Smoots