如何查询每种ENTRY_TYPE_NAME的最新Timestamp完整备份记录？

现有如下结构的备份表数据（来自SYS.M_BACKUP_CATALOG）：

'ENTRY_TYPE_NAME, STATE_NAME, TIMESTAMP',
'"log backup", "successful", "2022-07-25 12:11:20.965000000"',
'"complete data backup", "successful", "2022-07-22 11:39:56.757000000"',
'"complete data backup", "canceled", "2021-05-06 06:08:22.391000000"',
'"log backup", "failed", "2022-07-06 16:22:45.346000000"',
'"complete data backup", "failed", "2022-07-05 06:16:47.702000000"',

需要按ENTRY_TYPE_NAME分组，获取每组中Timestamp最新的完整记录（包含ENTRY_TYPE_NAME、STATE_NAME、Timestamp三个字段），期望输出：

'ENTRY_TYPE_NAME, STATE_NAME, TIMESTAMP',
'"complete data backup", "Successful", "2022-07-22 11:39:56.757000000"',
'"log backup", "Successful", "2022-07-25 12:11:20.965000000"',

原查询只能拿到分组后的类型和最大时间，无法关联对应的STATE_NAME：

select ENTRY_TYPE_NAME, MAX(UTC_END_TIME) as Timestamp from SYS.M_BACKUP_CATALOG GROUP BY ENTRY_TYPE_NAME

解决方法1：用窗口函数ROW_NUMBER() #

这是最直接的方案，通过窗口函数给每组内的记录按时间倒序排名，取排名第一的记录：

SELECT ENTRY_TYPE_NAME, STATE_NAME, UTC_END_TIME AS TIMESTAMP
FROM (
    SELECT 
        ENTRY_TYPE_NAME, 
        STATE_NAME, 
        UTC_END_TIME,
        ROW_NUMBER() OVER (PARTITION BY ENTRY_TYPE_NAME ORDER BY UTC_END_TIME DESC) AS rn
    FROM SYS.M_BACKUP_CATALOG
) t
WHERE rn = 1;

• 逻辑：PARTITION BY ENTRY_TYPE_NAME按备份类型分组，ORDER BY UTC_END_TIME DESC让每组内最新时间的记录排第1，外层筛选rn=1就能拿到目标完整记录。

解决方法2：用关联子查询 #

先通过子查询拿到每组的最大时间，再关联原表获取对应状态：

SELECT 
    b.ENTRY_TYPE_NAME, 
    b.STATE_NAME, 
    b.UTC_END_TIME AS TIMESTAMP
FROM SYS.M_BACKUP_CATALOG b
INNER JOIN (
    SELECT ENTRY_TYPE_NAME, MAX(UTC_END_TIME) AS max_time
    FROM SYS.M_BACKUP_CATALOG
    GROUP BY ENTRY_TYPE_NAME
) t ON b.ENTRY_TYPE_NAME = t.ENTRY_TYPE_NAME AND b.UTC_END_TIME = t.max_time;

• 逻辑：子查询先得到每个备份类型的最新时间，再通过类型和时间关联原表，匹配出对应的状态字段。

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