📊 Interviewer: How do you remove duplicate records in SQL?

👋 Me: We can remove duplicates using DISTINCT, GROUP BY, or delete duplicate rows using ROW_NUMBER().


✅ 1️⃣ Using DISTINCT (to fetch unique values)

SELECT DISTINCT column_name
FROM employees;

👉 Returns unique records but does not delete duplicates.


✅ 2️⃣ Using GROUP BY (to identify duplicates)

SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

👉 Helps find duplicate records.


✅ 3️⃣ Delete Duplicates Using ROW_NUMBER() (Most Important ⭐)
(Keeps one record and deletes others)

DELETE FROM employees
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (
             PARTITION BY name, salary
             ORDER BY id
           ) AS rn
    FROM employees
  ) t
  WHERE rn > 1
);

🧠 Logic Breakdown:

- DISTINCT → shows unique records
- GROUP BY → identifies duplicates
- ROW_NUMBER() → removes duplicates safely


✅ Use Case: Data cleaning, ETL processes, data quality checks.

💡 Tip: Always take a backup before deleting duplicate records.

💬 Tap ❤️ for more!