Access分组查询问题：获取各Case ID对应最新记录的File ID

问题说明 #

现有存储入库案件数据的表iktat，包含caseid（Case ID）、FileID（File ID）、date（Date）等字段，需要查询每个caseid对应的最新记录（即date最大的那条），获取其caseid、FileID和date。目前仅能通过分组查询得到caseid和最大日期，但无法关联对应FileID。

现有数据 #

期望结果 #

解决方案 #

方法1：子查询关联 #

先通过子查询获取每个caseid的最大日期，再将子查询结果与原表关联，匹配caseid和日期，从而得到对应的FileID：

SELECT i.caseid, i.FileID, i.date
FROM iktat i
INNER JOIN (
    SELECT caseid, MAX(date) AS MaxOfdate
    FROM iktat
    GROUP BY caseid
) sub 
ON i.caseid = sub.caseid AND i.date = sub.MaxOfdate;

方法2：窗口函数（推荐） #

使用ROW_NUMBER()窗口函数，按caseid分组，每组内按date降序排序，取排序后编号为1的记录（即最新记录）：

SELECT caseid, FileID, date
FROM (
    SELECT 
        caseid, 
        FileID, 
        date,
        ROW_NUMBER() OVER (PARTITION BY caseid ORDER BY date DESC) AS rn
    FROM iktat
) t
WHERE rn = 1;

注意：如果同一个caseid存在多条date相同的最新记录，方法1会返回所有符合条件的记录；方法2默认只返回一条，若需返回全部相同日期的最新记录，可将ROW_NUMBER()替换为RANK()。

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