How to delete duplicate records while keeping the latest one?

[suyXcode]

Hi 👋

I have a table with duplicate email values and a created_at timestamp column.

I want to remove duplicates but keep only the most recent record for each email.

What is the best SQL way to do this in MySQL?

Thanks 🙏

[CodingwithAnkit-tech]

You can use a window function with ROW_NUMBER().

Example table: users(id, email, created_at)

DELETE FROM users
WHERE id NOT IN (
SELECT id FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
FROM users
) t
WHERE rn = 1
);

This assigns a rank inside each email group and keeps only the newest record (rn = 1).