SQL Query to Remove Duplicate Records While Keeping Latest Entry

🧹 SQL Query to Remove Duplicate Records While Keeping the Latest Entry

Duplicate data is one of the most common problems in database management. In this tutorial, you’ll learn how to remove duplicate records while keeping only the latest entry — based on a timestamp or unique ID. This technique works in MySQL, SQL Server, Oracle, and PostgreSQL.

We’ll use a practical example with an orders table to demonstrate how to identify duplicates and safely delete older ones using ROW_NUMBER() and CTE (Common Table Expressions).

🧩 Sample Data: Orders Table

order_id	customer_id	order_date	order_amount
101	C001	2025-11-01	500
102	C001	2025-11-03	700
103	C002	2025-10-30	450
104	C002	2025-11-04	600

Notice that customer C001 and C002 have duplicate entries — we’ll keep only the most recent one.

💻 SQL Query: Keep Only Latest Record Using ROW_NUMBER()

WITH ranked_orders AS (
  SELECT order_id, customer_id, order_date, order_amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
DELETE FROM ranked_orders
WHERE rn > 1;

💡 This query assigns a rank to each order grouped by customer_id, ordered by the latest order_date. Then it deletes all records except the first one (rn = 1), effectively removing duplicates.

📊 Example Result

order_id	customer_id	order_date	order_amount
102	C001	2025-11-03	700
104	C002	2025-11-04	600

✅ Both customers now have only their most recent orders remaining in the table.

⚙️ Bonus: Find Duplicates Without Deleting

If you only want to identify duplicates (not delete them), use this query instead:

SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

This will list all customers having duplicate orders so you can manually review before running the delete command.

🚀 Performance Tips

• Always test the SELECT version of your DELETE query before executing it.
• Add indexes on customer_id and order_date for faster filtering.
• Use transactions (BEGIN TRANSACTION) when working on production databases.

📘 Recommended Books to Master SQL & Data Cleaning

• 📗 Learning SQL: Generate, Manipulate, and Retrieve Data, Third Edition
• 🤖 AI Engineering: Building Applications with Foundation Models
• 📊 Storytelling With Data: A Data Visualization Guide for Business Professionals
• 🟡 Microsoft Power BI for Dummies
• 🧩 The Definitive Guide to DAX (2nd Edition)

These books are perfect for mastering SQL data management and reporting automation in Power BI.

🔗 Related SQL Tutorials

• Find Duplicate Records with Different Values in SQL
• SQL Query to Calculate Running Total Without Window Functions
• SQL Query to Find Employees Who Didn’t Attend Any Training

💬 Join the SQL Community

Have a SQL challenge or want to share your query? Join our discussion forum 👉 SQLQueries.in Community Forum Connect with SQL learners, data engineers, and BI professionals.