SQL Query to delete duplicate records but keep latest

June 24, 2023

I need a query to delete all the duplicate records but just to keep one latest record based on valid_until date column. I tried with the below but it says 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
)

>Solution :

This is example code for that. Can you check this answer please.

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;

Click this