Checking for Duplicate Rows – Short note

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

Name                              Department
Information Tech         IT
it                                       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.

👉 Approaches

1️⃣ Finding duplicate for single column

SELECT DEPT_NAME, COUNT(*) FROM employee GROUP BY DEPT_NAME
HAVING COUNT(*) > 1;

DEPT_NAME       COUNT(*)
IT                            4
HR                          5
ADMIN                  7

2️⃣ Will number the duplicate rows with the row_number window function

SELECT row_number() over (partition by DEPT_NAME),
    EMP_NAME, DEPT_NAME
  FROM employee;

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,
EMP_NAME, DEPT_NAME
  FROM employee) t 
WHERE t.row_n < 2;

Related posts:

SQL Where Clause – Short note SQL Window Function – part 1 Learn MySQL Important Queries – Part 2 Learn MySQL Important Queries – Part 1