删除重复记录但保留最新的SQL查询

发布于06月24日

我需要一个查询来删除所有重复的记录，但只保留一个基于valid_until日期列的最新记录.我试了试下面的，但上面写着An expression of non-boolean type specified in a context where a condition is expected, near ','.

DELETE FROM tableOne
WHERE (id, valid_until ) NOT IN (
  SELECT id, MAX(valid_until )
  FROM tableOne
  GROUP BY id
)

推荐答案

您可以使用可更新的CTE.

CREATE TABLE Employees (
  id INT,
  name VARCHAR(50),
  valid_until DATE
);

INSERT INTO Employees (id, name, valid_until) VALUES
  (1, 'John Doe', '2022-06-30'),
  (2, 'Jane Smith', '2023-01-15'),
  (3, 'Michael Johnson', '2021-12-31'),
  (1, 'John Doe', '2023-06-30'),
  (2, 'Jane Smith', '2024-03-22');

WITH CTE AS (
  SELECT
    id,
    valid_until,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY valid_until DESC) AS row_num
  FROM Employees
)
DELETE FROM CTE 
WHERE row_num > 1;

SQL Fiddle个