如何从含重复ID的数据库表中获取各ID的最新记录？

问题场景 #

需要从存在重复行的数据库表中，提取每个ID对应的最新记录。每月仅部分ID会新增数据，其余ID无更新。

示例数据 #

ID   Date
1    8/30/2022
1    7/30/2022
3    8/30/2022
3    7/30/2022
3    6/30/2022 
4    1/11/2021

期望查询结果 #

ID   Date
1    8/30/2022
3    8/30/2022
4    1/11/2021

错误尝试及问题分析 #

尝试用子查询关联的方式获取数据，但返回结果仅包含全表最新日期的记录，而非每个ID各自的最新记录。错误语句如下：

create table  test as (
select * from table1 inner join
(select EmpID, max(Record_Date) as maxdate
from table1 group by EmpID) ms
on table1.EmpID ms.EmpID and Record_Date=maxdate)
 WITH DATA;

问题出在关联条件语法错误：on table1.EmpID ms.EmpID缺少了等于号，导致关联逻辑失效，进而返回不符合预期的结果。

正确解决方案 #

方法1：修复关联条件的子查询方式 #

补上关联条件中缺失的等于号，即可得到正确结果：

create table test as (
select table1.* 
from table1 
inner join (
    select EmpID, max(Record_Date) as maxdate
    from table1 
    group by EmpID
) ms on table1.EmpID = ms.EmpID and table1.Record_Date = ms.maxdate
) WITH DATA;

方法2：使用窗口函数（简洁高效） #

如果数据库支持窗口函数（如DB2、PostgreSQL、SQL Server等），可以用ROW_NUMBER()函数直接筛选每个ID的最新记录：

create table test as (
select t.ID, t.Record_Date
from (
    select *,
           ROW_NUMBER() over (partition by EmpID order by Record_Date desc) as rn
    from table1
) t
where t.rn = 1
) WITH DATA;

partition by EmpID按ID分组，order by Record_Date desc让每组内最新日期的记录排在首位，取rn=1的行即为每个ID的最新记录。

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