How to find Duplicate Rows in MySQL

Hi All,

Last day, I was stuck in database query, because I got multiply redundant rows in my database table. So I search a lot but I didn’t get any effective solution to find out redundant rows in my table. So somehow I got query to solve my problem.

Let’s try to understand the query.

First of All

Step 1. We have following table.

+----+---------+-----------+
| id | name    | mobile_no |
+----+---------+-----------+
|  1 | John    |     12345 |
|  2 | Maya    |     54321 |
|  3 | Roy     |     98765 |
|  4 | Tony    |     12345 |
|  5 | Harry   |     12345 |
|  6 | Sheldon |     12345 |
|  7 | xyz     |     54321 |
+----+---------+-----------+

We have 3 columns id, name, and mobile_no.

We want to find that rows which have same mobile_no.

To find redundant “mobile_no”, we have SQL following.

 

SELECT  *

FROM `testtbl`

WHERE `mobile_no` IN (

SELECT `mobile_no`

FROM `testtbl`

GROUP BY `mobile_no`

HAVING COUNT(`mobile_no`) > 1

)

Output:-

+----+---------+-----------+
| id | name    | mobile_no |
+----+---------+-----------+
|  1 | John    |     12345 |
|  2 | Maya    |     54321 |
|  4 | Tony    |     12345 |
|  5 | Harry   |     12345 |
|  6 | Sheldon |     12345 |
|  7 | xyz     |     54321 |
+----+---------+-----------+

So we got that rows which have redundant or we can say duplicate mobile_no


 

Let’s Understand this query that how it works.

The query is simple up-to this.

……………………………………..

SELECT  *

FROM `testtbl`

WHERE `mobile_no` IN ( …..

………………………………………

Now “IN “clause, IN clause is just like OR operator.

Click here to know How IN clause Works

Next is:-

……………………………………….

SELECT `mobile_no`

FROM `testtbl`

GROUP BY `mobile_no`

HAVING COUNT(`mobile_no`) > 1

…………………………………………

We know that GROUP BY clause will group the same data in that column, where we are applying GROUP BY clause.

In our Example We use column “mobile_no”, now GROUP By clause return different kinds of group according to “mobile_no”.

Now we are using HAVING clause which use aggregation functions. So we are using count aggregation function, which returns the count in different kinds of groups generated by GROUP BY clause.

Now condition “Greater Than 1”

HAVING clause will work with count function and return count. This count is the most important part of result because this count will tell us how many rows are available with same “mobile_no” using condition

{…. COUNT (`mobile_no`) > 1 ….}.

So this is all about to find duplicate Rows in a table of MySQL database.

I hope that you understand the logic of the query that we used to find duplicate rows.

 

No Comments

Post a Comment