How to delete duplicate records while keeping the latest one? #6
|
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 🙏 |
Answered by
ankitiqcode
Dec 28, 2025
Replies: 1 comment
|
You can use a window function with ROW_NUMBER(). Example table: users(id, email, created_at) DELETE FROM users This assigns a rank inside each email group and keeps only the newest record (rn = 1). |
0 replies
Answer selected by
suyXcode
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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).