SQL查询结果去重：获取指定CaseId的最新时间戳单条记录

原查询因LEFT JOIN关联出多条匹配记录导致返回重复行，要获取包含最新EntryDate、pyStatusWorkTimeStamp和CMPR_TIMESTAMP的单行数据，可通过以下几种方式实现：

通用方案（支持多数数据库：MySQL 8+、PostgreSQL、SQL Server等） #

使用窗口函数ROW_NUMBER()标记最新行：

SELECT AccountNumber, CaseId, EntryDate, pyStatusWorkTimeStamp, CMPR_TIMESTAMP
FROM (
    SELECT 
        D.AccountNumber, D.CaseId, D.EntryDate, 
        D.pyStatusWorkTimeStamp, L.CMPR_TIMESTAMP,
        ROW_NUMBER() OVER (ORDER BY D.EntryDate DESC, D.pyStatusWorkTimeStamp DESC, L.CMPR_TIMESTAMP DESC) AS rn
    FROM dispute_archive D 
    LEFT JOIN cmpr_letter_archive L ON D.CaseId = L.FULL_CASEID
    WHERE D.CaseId = 'D-1000130'
) t
WHERE rn = 1;

• 内层查询按三个时间戳倒序排序，给每一行分配序号rn，最新的行序号为1
• 外层筛选rn=1的行，即为目标单行结果

简化方案（按数据库类型选择） #

MySQL/MariaDB #

直接用LIMIT 1：

SELECT 
    D.AccountNumber, D.CaseId, D.EntryDate, 
    D.pyStatusWorkTimeStamp, L.CMPR_TIMESTAMP
FROM dispute_archive D 
LEFT JOIN cmpr_letter_archive L ON D.CaseId = L.FULL_CASEID
WHERE D.CaseId = 'D-1000130'
ORDER BY D.EntryDate DESC, D.pyStatusWorkTimeStamp DESC, L.CMPR_TIMESTAMP DESC
LIMIT 1;

PostgreSQL/DB2 #

用FETCH FIRST 1 ROW ONLY：

SELECT 
    D.AccountNumber, D.CaseId, D.EntryDate, 
    D.pyStatusWorkTimeStamp, L.CMPR_TIMESTAMP
FROM dispute_archive D 
LEFT JOIN cmpr_letter_archive L ON D.CaseId = L.FULL_CASEID
WHERE D.CaseId = 'D-1000130'
ORDER BY D.EntryDate DESC, D.pyStatusWorkTimeStamp DESC, L.CMPR_TIMESTAMP DESC
FETCH FIRST 1 ROW ONLY;

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