Hi folks here is the short tip to check the duplicate data in MySQL.
Checking for Duplicate Rows
To prevent duplicate rows in a database table is to put a unique index on the column, but sometime comes as dirty and table got duplicate rows.
#️⃣ Suppose we have table which store name and department for the users, but sometime with different name rows with same department
Information Tech IT
Human resource HR
In above table you can see we have three data where two rows are duplicate – Department is same but names are different.
✅Best way to find duplicate rows is by using WINDOW FUNCTIONS.
1️⃣ Finding duplicate for single column
SELECT DEPT_NAME, COUNT(*) FROM employee GROUP BY DEPT_NAME
HAVING COUNT(*) > 1;
2️⃣ Will number the duplicate rows with the row_number window function
SELECT row_number() over (partition by DEPT_NAME),
3️⃣ Finally will wrap the query and filtering out the rows with row_number column having a value greater than 1.
SELECT * FROM
( SELECT row_number() over (partition by dept_name) as row_n,
FROM employee) t
WHERE t.row_n < 2;