SQL find row duplicates

Question

I have a MySQL database similar to:

+----+---------+---------+------------------+....
| id | unique1 | unique2 |   genaric_data   |....
+----+---------+---------+------------------+....
| 0  |   100   |   1C7   | {data container} |....
+----+---------+---------+------------------+....
| 1  |   100   |   1C7   | {data container} |....
+----+---------+---------+------------------+....
| 2  |   100   |   1C8   | {data container} |....
+----+---------+---------+------------------+....
| 3  |   101   |   ---   | {data container} |....
+----+---------+---------+------------------+....
| 4  |   102   |   0     | {data container} |....
+----+---------+---------+------------------+....
| 5  |   103   |   1     | {data container} |....
.................................................

I need a way to add an extra column that gives the number of times all unique fields are used. I will then need to clean up the data manually.

I want a query to return:

+----+---------+---------+------+------------------+....
| id | unique1 | unique2 | dupe |   genaric_data   |....
+----+---------+---------+------+------------------+....
| 0  |   100   |   1C7   |   2  | {data container} |....
+----+---------+---------+------+------------------+....
| 1  |   100   |   1C7   |   2  | {data container} |....
+----+---------+---------+------+------------------+....
| 2  |   100   |   1C8   |   1  | {data container} |....
+----+---------+---------+------+------------------+....
| 3  |   101   |   ---   |   1  | {data container} |....
+----+---------+---------+------+------------------+....
| 4  |   102   |   0     |   1  | {data container} |....
+----+---------+---------+------+------------------+....
| 5  |   103   |   1     |   1  | {data container} |....
.......................................................

This has been a problem I have had for a while and currently my only solution is to export the data to excel and use it to find the duplicates.

Thanks.

Edit: The possible duplicate is not a solution to my problem since when I execute:

SELECT *,count(*) FROM `database`
GROUP BY  `unique1`
HAVING count(*) > 1

On PhpMyAdmin(All I'm allowed access to) it merges anything with the same unique1 into one line.

Just use a GROUP BY unique2 along with a select count(*) and then JOIN back to the table you have already. — Eric Hotinger
– Eric Hotinger, Commented Apr 23, 2015 at 18:56
I did try using group by, and count, this does give me the desired effect, but also doesn't leave the rows separate in the query — theshadow124
– theshadow124, Commented Apr 23, 2015 at 19:06

The Conspiracy · Accepted Answer · 2015-04-24 20:14:13Z

3

The solution to your problem is to use GROUP BY:

SELECT unique1, unique2, Count(*) As colCount FROM YourTable
GROUP BY unique1, unique2
HAVING Count(*) > 1

This will return all combinations of unique1 and unique2 that occur more than once.

In a second step, you can build a query that returns all affected rows.

SELECT YourTable.*, rstDuplicates.colCount 
FROM YourTable INNER JOIN (
  SELECT unique1, unique2, Count(*) As colCount FROM YourTable
  GROUP BY unique1, unique2
  HAVING Count(*) > 1
) As rstDuplicates ON YourTable.unique1 = rstDuplicates.unique1 And YourTable.unique2 = rstDuplicates.unique2

This will output all rows that have at least one duplicate. The colCount column shows the number of appearances.

edited Apr 24, 2015 at 20:14

answered Apr 23, 2015 at 19:18

The Conspiracy

4,0431 gold badge20 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2015-04-23 19:27:21Z

1

If you want to add a field with the information, a correlated subquery is perhaps the easiest way:

select t.*,
       (select count(*)
        from table t2
        where t2.unique1 = t.unique1 and t2.unique2 = t.unique2
       ) as dupecnt
from table t;

Sometimes, this is efficient (with an index on unique1, unique2. Sometimes, it is more efficient to do the aggregation in the from clause:

select t.*, t2.dupecnt
from table t join
     (select unique1, unique2, count(*) as dupecnt
      from table t2
      group by unique1, unique2
     ) t2
     on t2.unique1 = t.unique1 and t2.unique2 = t.unique1

answered Apr 23, 2015 at 19:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

theshadow124 Over a year ago

This worked as well, but I can only mark one as the answer and I didnt see yours until after I had marked "The Conspiracy"'s answer. Thank you for your answer!

Collectives™ on Stack Overflow

SQL find row duplicates

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related