如何删除test_tab表中重复FIRSTNAME的行，仅保留TS最大的记录？

你创建的表结构如下：

CREATE TABLE test_tab(
ID INT,
FIRSTNAME VARCHAR(40),
TS TIMESTAMP)

插入的测试数据：

INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (1, 'Jhon', '2018-06-05 00:11:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (2, 'Jhon', '2018-06-15 00:14:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (3, 'Jhon', '2018-06-19 00:10:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (4, 'Mike', '2018-06-05 00:10:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (5, 'Mike', '2018-06-15 00:10:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (6, 'Mike', '2018-06-20 00:10:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (7, 'Lis', '2018-06-05 00:13:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (8, 'Lis', '2018-06-15 00:17:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (9, 'Lis', '2018-06-21 00:10:56');

你的目标是每个FIRSTNAME仅保留TS值最大的一行，删除其余重复行。你写的查询仅能查询出部分FIRSTNAME，无法实现删除操作，下面提供几种可行的实现方式：

方法1：子查询匹配最大TS记录 #

先找出每个FIRSTNAME对应的最大TS值，再删除表中不属于这些（FIRSTNAME+最大TS）组合的记录：

DELETE FROM test_tab
WHERE (FIRSTNAME, TS) NOT IN (
    SELECT FIRSTNAME, MAX(TS)
    FROM test_tab
    GROUP BY FIRSTNAME
);

方法2：使用窗口函数标记保留行 #

通过ROW_NUMBER()窗口函数给每个FIRSTNAME分组内的记录按TS降序编号，只保留编号为1的记录，删除其余：

DELETE FROM test_tab
WHERE ID IN (
    SELECT ID
    FROM (
        SELECT 
            ID,
            ROW_NUMBER() OVER (PARTITION BY FIRSTNAME ORDER BY TS DESC) AS rn
        FROM test_tab
    ) t
    WHERE rn > 1
);

注：此方法依赖ID为唯一标识，若ID存在重复，可改用表中的主键或组合唯一键进行筛选。

方法3：自连接对比TS #

通过自连接找到每个FIRSTNAME中TS不是最大的记录并删除：

DELETE t1
FROM test_tab t1
JOIN test_tab t2 ON t1.FIRSTNAME = t2.FIRSTNAME AND t1.TS < t2.TS;

这种方式会直接删除所有TS小于同组内最大TS的记录，逻辑简洁高效。

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