« Five great Perl programming techniques to make your life fun again
How to delete duplicate rows with SQL »

How to find duplicate rows with SQL

Published at October 9, 2006 in SQL. Tags: No Tags.

This article shows how to find duplicated rows in a database table. This is a very common beginner question. The basic technique is straightforward. I’ll also show some variations, such as how to find “duplicates in two columns” (a recent question on the #mysql IRC channel).

How to find duplicated rows

The first step is to define what exactly makes a row a duplicate of another row. Most of the time this is easy: they have the same value in some column. I’ll take this as a working definition for this article, but you may need to alter the queries below if your notion of “duplicate” is more complicated.

For this article, I’ll use this sample data:

create table test(id int not null primary key, day date not null);

insert into test(id, day) values(1, '2006-10-08');
insert into test(id, day) values(2, '2006-10-08');
insert into test(id, day) values(3, '2006-10-09');

select * from test;
+----+------------+
| id | day        |
+----+------------+
|  1 | 2006-10-08 |
|  2 | 2006-10-08 |
|  3 | 2006-10-09 |
+----+------------+

The first two rows have the same value in the day column, so if I consider those to be duplicates, here’s a query to find them. The query uses a GROUP BY clause to put all the rows with the same day value into one “group” and then count the size of the group:

 select day, count(*) from test GROUP BY day;
+------------+----------+
| day        | count(*) |
+------------+----------+
| 2006-10-08 |        2 |
| 2006-10-09 |        1 |
+------------+----------+

The duplicated rows have a count greater than one. If you only want to see rows that are duplicated, you need to use a HAVING clause (not a WHERE clause), like this:

select day, count(*) from test group by day HAVING count(*) > 1;
+------------+----------+
| day        | count(*) |
+------------+----------+
| 2006-10-08 |        2 |
+------------+----------+

This is the basic technique: group by the column that contains duplicates, and show only those groups having more than one row.

Why can’t you use a WHERE clause?

A WHERE clause filters the rows before they are grouped together. A HAVING clause filters them after grouping. That’s why you can’t use a WHERE clause in the above query.

How to delete duplicate rows

A related question is how to delete the ‘duplicate’ rows once you find them. A common task when cleaning up bad data is to delete all but one of the duplicates, so you can put proper indexes and primary keys on the table, and prevent duplicates from getting into the table again.

Again, the first thing to do is make sure your definition is clear. Exactly which row do you want to keep? The ‘first’ one? The one with the largest value of some column? For this article, I’ll assume you want to keep the ‘first’ row — the one with the smallest value of the id column. That means you want to delete every other row.

Probably the easiest way to do this is with a temporary table. Especially in MySQL, there are some restrictions about selecting from a table and updating it in the same query. You can get around these, as I explain in my article How to select from an update target in MySQL, but I’ll just avoid these complications and use a temporary table.

The exact definition of the task is to delete every row that has a duplicate, except the row with the minimal value of id for that group. So you need to find not only the rows where there’s more than one in the group, you also need to find the row you want to keep. You can do that with the MIN() function. Here are some queries to create the temporary table and find the data you need to do the DELETE:

create temporary table to_delete (day date not null, min_id int not null);

insert into to_delete(day, min_id)
   select day, MIN(id) from test group by day having count(*) > 1;

select * from to_delete;
+------------+--------+
| day        | min_id |
+------------+--------+
| 2006-10-08 |      1 |
+------------+--------+

Now that you have this data, you can proceed to delete the ‘bad’ rows. There are many ways to do this, and some are better than others (see my article about many-to-one problems in SQL), but again I’ll avoid the finer points and just show you a standard syntax that ought to work in any RDBMS that supports subqueries:

delete from test
   where exists(
      select * from to_delete
      where to_delete.day = test.day and to_delete.min_id <> test.id
   )

If your RDBMS does not support subqueries, or if it’s more efficient, you may wish to do a multi-table delete. The syntax for this varies between systems, so you need to consult your system’s documentation. You may also need to do all of this in a transaction to avoid other users changing the data while you’re working, if that’s a concern.

How to find duplicates in multiple columns

Someone recently asked a question similar to this on the #mysql IRC channel:

I have a table with columns b and c that links two other tables b and c, and I want to find all rows that have duplicates in either b or c.

It was difficult to understand exactly what this meant, but after some conversation I grasped it: the person wanted to be able to put unique indexes on columns b and c separately.

It’s pretty easy to find rows with duplicate values in one or the other column, as I showed you above: just group by that column and count the group size. And it’s easy to find entire rows that are exact duplicates of other rows: just group by as many columns as you need. But it’s harder to identify rows that have either a duplicated b value or a duplicated c value. Take the following sample table, which is roughly what the person described:

create table a_b_c(
   a int not null primary key auto_increment,
   b int,
   c int
);

insert into a_b_c(b,c) values (1, 1);
insert into a_b_c(b,c) values (1, 2);
insert into a_b_c(b,c) values (1, 3);
insert into a_b_c(b,c) values (2, 1);
insert into a_b_c(b,c) values (2, 2);
insert into a_b_c(b,c) values (2, 3);
insert into a_b_c(b,c) values (3, 1);
insert into a_b_c(b,c) values (3, 2);
insert into a_b_c(b,c) values (3, 3);

Now, you can easily see there are some ‘duplicate’ rows in this table, but no two rows actually have the same tuple {b, c}. That’s why this is a bit more difficult to solve.

Queries that don’t work

If you group by two columns together, you’ll get various results depending on how you group and count. This is where the IRC user was getting stumped. Sometimes queries would find some duplicates but not others. Here are some of the things this person tried:

select b, c, count(*) from a_b_c
group by b, c
having count(distinct b > 1)
   or count(distinct c > 1);

This query returns every row in the table, with a COUNT(*) of 1, which seems to be wrong behavior, but it’s actually not. Why? Because the > 1 is inside the COUNT(). It’s pretty easy to miss, but this query is actually the same as

select b, c, count(*) from a_b_c
group by b, c
having count(1)
   or count(1);

Why? Because (b > 1) is a boolean expression. That’s not what you want at all. You want

select b, c, count(*) from a_b_c
group by b, c
having count(distinct b) > 1
   or count(distinct c) > 1;

This returns zero rows, of course, because there are no duplicate {b, c} tuples. The person tried many other combinations of HAVING clauses and ORs and ANDs, grouping by one column and counting the other, and so forth:

select b, count(*) from a_b_c group by b having count(distinct c) > 1;
+------+----------+
| b    | count(*) |
+------+----------+
|    1 |        3 |
|    2 |        3 |
|    3 |        3 |
+------+----------+

Nothing found all the duplicates, though. What I think made it most frustrating is that it partially worked, making the person think it was almost the right query… perhaps just another variation would get it…

In fact, it’s impossible to do with this type of simple GROUP BY query. Why is this? It’s because when you group by one column, you distribute like values of the other column across multiple groups. You can see this visually by ordering by those columns, which is what grouping does. First, order by column b and see how they are grouped:

When you order (group) by column b, the duplicate values in column c are distributed into different groups, so you can’t count them with COUNT(DISTINCT c) as the person was trying to do. Aggregate functions such as COUNT() only operate within a group, and have no access to rows that are placed in other groups. Similarly, when you order by c, the duplicate values in column b are distributed into different groups. It is not possible to make this query do what’s desired.

Some correct solutions

Probably the simplest solution is to find the duplicates for each column separately and UNION them together, like this:

select b as value, count(*) as cnt, 'b' as what_col
 from a_b_c group by b having count(*) > 1
 union
 select c as value, count(*) as cnt, 'c' as what_col
 from a_b_c group by c having count(*) > 1;
+-------+-----+----------+
| value | cnt | what_col |
+-------+-----+----------+
|     1 |   3 | b        |
|     2 |   3 | b        |
|     3 |   3 | b        |
|     1 |   3 | c        |
|     2 |   3 | c        |
|     3 |   3 | c        |
+-------+-----+----------+

The what_col column in the output indicates what column the duplicate value was found in. Another approach is to use subqueries:

select a, b, c from a_b_c
 where b in (select b from a_b_c group by b having count(*) > 1)
    or c in (select c from a_b_c group by c having count(*) > 1);
+----+------+------+
| a  | b    | c    |
+----+------+------+
|  7 |    1 |    1 |
|  8 |    1 |    2 |
|  9 |    1 |    3 |
| 10 |    2 |    1 |
| 11 |    2 |    2 |
| 12 |    2 |    3 |
| 13 |    3 |    1 |
| 14 |    3 |    2 |
| 15 |    3 |    3 |
+----+------+------+

This is probably much less efficient than the UNION approach, and will show every duplicated row, not just the values that are duplicated. Still another approach is to do self-joins against grouped subqueries in the FROM clause. This is more complicated to write correctly, but might be necessary for some complex data, or for efficiency:

select a, a_b_c.b, a_b_c.c
from a_b_c
   left outer join (
      select b from a_b_c group by b having count(*) > 1
   ) as b on a_b_c.b = b.b
   left outer join (
      select c from a_b_c group by c having count(*) > 1
   ) as c on a_b_c.c = c.c
where b.b is not null or c.c is not null

Any of these queries will do, and I’m sure there are other ways too. If you can use UNION, it’s probably the easiest.

Technorati Tags:No Tags

You might also like:

  1. How to delete duplicate rows with SQL, Part 2
  2. How to delete duplicate rows with SQL
  3. How to group data in SQL with a catch-all bucket
  4. How to implement a queue in SQL
  5. How to write INSERT IF NOT EXISTS queries in standard SQL

53 Responses to “How to find duplicate rows with SQL”

Feed for this Entry Trackback Address
  1. 1 Dmitri Mikhailov
    Oct 10th, 2006 at 1:11 pm

    Why can’t you use a WHERE clause?

    As a matter of fact we can as: - SQL is an extremely redundant language - HAVING clause is one of the redundancies

    select day
     , min(id)
     , count(*)
  from test
 where 1 < (select count(*)
              from test t_dup
             where t_dup.day = test.day
           )
 group by day
  2. 2 Xaprb
    Oct 10th, 2006 at 1:28 pm

    Well spoken! Still, it’s not quite the same thing. You wrote a correlated subquery. I was speaking about why you can’t use a WHERE clause like this:

    select ... WHERE COUNT(*) > 1

    This is a common thing beginners try before they learn about the HAVING clause. I just wanted to address that question along the way.

  3. 3 balluche
    Oct 10th, 2006 at 5:26 pm

    It’s possible to remove duplicates from table test with just one query, use:

    select * from test group by day HAVING count(distinct id > 1)
  4. 4 Xaprb
    Oct 10th, 2006 at 10:08 pm

    That query doesn’t do what you think — run it and see. You are right though, it’s possible to remove duplicates with a single query. I meant to keep this very basic for beginners, but maybe it’s a good idea for me to fill in some techniques about single-query techniques. For one thing, they eliminate the need for a transaction.

  5. 5 Matt Bunter
    Oct 11th, 2006 at 9:15 am

    Excellent, just excellent. Good explanations and examples.

    Thank you.

  6. 6 balluche
    Oct 12th, 2006 at 4:42 pm

    Well, I may not understand but for example with this table

    1	2006-10-08
2	2006-10-08
3	2006-10-09
4	2006-10-08
5	2006-10-09
6	2006-10-10
7	2006-10-11

    Result of the query above is

    1	2006-10-08
3	2006-10-09
6	2006-10-10
7	2006-10-11

    Distinct is not necessary. I may be wrong but I would like to know how you remove duplicates with one query. Thanks !

  7. 7 Xaprb
    Oct 12th, 2006 at 5:11 pm

    balluce, my next article is about how to remove duplicates (I wrote it since you first commented). I hope that helps.

    About your query, there are two things happening that aren’t the way they seem. One is that you are selecting non-GROUPed columns in a GROUPed statement. This is something MySQL allows you to do, but it is technically not correct. It confuses people a lot. If you know how MySQL works, you can use it, but it is still a bad idea in my opinion. You should definitely not ever use it unless you have a firm grasp on the mathematics of why it is technically invalid.

    The other thing is your query is using the COUNT() function incorrectly. COUNT() counts defined values, not “true” values. COUNT(id > 1) is really the same as “count the number of times the expression ‘a row has an id greater than one’ has a defined value,” not “count the number of times id is greater than one.” The result of the boolean expression “this id value is greater than 1″ is always a defined value unless id is NULL, so even when id is not greater than one, it still contributes to the count. To make this clearer, try this query:

    select count(0 > 1) from test;

    The result is not what you expected, is it? You need to put the “greater than one” outside the COUNT() function.

    This is why your query returns a row for 2006-10-10 even though that date appears only once in the table, and is hence not a duplicate.

  8. 8 balluche
    Oct 13th, 2006 at 12:39 pm

    Thank you for your response. You’re right, the HAVING CLAUSE is not necessary obviously. Therefore my query doesn’t remove duplicate but display non duplicate lines. It’s possible then to save the result in a temporary table and to upload data to the original table.

    More portable query is:


    select min(id), day from test group by day

  9. 9 Lynn MacFarland
    Nov 3rd, 2006 at 8:14 pm

    I have a complicated SQL query which puts the following information into a temporary table which I then sort and return to display on the web (intranet):

    TrackingID, DetailID, Requester, ServiceDirector, MovedDate, Location, …

    Sometimes there are duplicates - same TrackingID & DetailID (same TrackingID and unique DetailID is not a duplicate). In fact the “duplicate” only differs by ServiceDirector. I want the “last” ServiceDirector - not an order by ServiceDirector but the last displayed in the query.

    Any ideas on how to return the record with the “last” ServiceDirector from the stord procedrue?

  10. 10 Xaprb
    Nov 3rd, 2006 at 9:17 pm

    I have a draft of another post about this, but you can do a self-join:

    select tbl.* from tbl
   inner join (
      select TrackingID, DetailID, max(ServiceDirector) as ServiceDirector
      from tbl group by TrackingID, DetailID
   ) as x on x.TrackingID = tbl.TrackingID
      and x.DetailID = tbl.DetailID
      and x.ServiceDirector = tbl.ServiceDirector

    Basically, group the subquery by whatever is unique in the table, and then join that result against the full table on the unique columns plus the max() or min().

    You can also do a correlated subquery, but test the performance carefully — it may be very bad!

    select * from tbl
where ServiceDirector = (
   select max(ServiceDirector)
   from tbl as tbl2
   where tbl.TrackingID = tbl2.TrackingID
      and tbl.DetailID = tbl2.DetailID)
  11. 11 Nom
    Nov 9th, 2006 at 1:01 pm

    Hi there, I have a similar issue but slightly complicated. I have 3 tables.

    select * from Communications;
+-----------------+----------+--------------+-------------+
| CommunicationID | EntityID | EntityTypeID | Detail      |
+-----------------+----------+--------------+-------------+
|               3 |       11 |            8 | abc@abc.com |
|               4 |        2 |           13 | abc@abc.com |
|               5 |       12 |            8 | xyz@abc.com |
|               6 |        3 |           13 | abc@abc.com |
+—————–+———-+————–+————-+

select * from Sites;
+——–+————+
| SiteID | SupplierID |
+——–+————+
|      2 |         11 |
|      3 |         12 |
+——–+————+

select * from Suppliers;
+————+————–+
| SupplierID | SupplierName |
+————+————–+
|         11 | Jims         |
|         12 | XYZ          |
+————+————–+

    Sites belong to a supplier. Communications Table keeps the Communication details of both the Sites and the suppliers. EntityID is the ID of either the supplier or the Site, which is determined by the EntityTypeID. Entity type ID 8 = Supplier and 13 = Site.

    As you can see that communication table can hold both the sites and suppliers communication details, and in some cases the supplier details and the sites details in table communication detail column can be the same as shown above (abc@abc.com) thus duplicating the value. I want to get a list of the duplicate values and then delete them. Duplicate values are only those where the supplier owns the site, meaning if the Site is not of the same supplier as shown in Site table (which there is 11) than even if the values are same its not duplicating.

    To put in Simple Terms, Supplier 11 has Communication Detail abc@abc.com in the Communication table.
    and owns Site 2 which also has Detail in Communications Table abc@abc.com. ( the Detail must be same if its different then its ok eg if the Detail of Site 2 is def@abc.com then its fine)

    So this is duplication which i wana list out and remove. And if Site 3 has the same Detail in the Communications Table abc@abc.com, and is owned by Supplier 12 its not duplication. as the supplier is different. So Communications Record 4 is not duplication but record 1 and 2 has duplicate details as the Supplier and Sites are related.

    Please help me if you understand it and if its not clear let me know.

    Regards
    Nom

  12. 12 Xaprb
    Nov 12th, 2006 at 11:13 pm

    Nom, if I’m understanding you correctly, the following query makes it obvious which entries are duplicated:

    select Detail, coalesce(SupplierID, EntityID) as SupplierID
from Communications
    left outer join Sites on EntityID = SiteID and EntityTypeID = 13

+-------------+------------+
| Detail      | SupplierID |
+-------------+------------+
| abc@abc.com |         11 |
| abc@abc.com |         11 |
| xyz@abc.com |         12 |
| abc@abc.com |         12 |
+————-+————+

    You can apply the techniques I discussed to this result set. For example:

    select min(CommunicationID) as MinID,
      Detail, coalesce(SupplierID, EntityID) as SupplierID
from Communications
    left outer join Sites on EntityID = SiteID and EntityTypeID = 13
group by Detail, coalesce(SupplierID, EntityID)
having count(*) > 1;

+-------+-------------+------------+
| MinID | Detail      | SupplierID |
+-------+-------------+------------+
|     3 | abc@abc.com |         11 |
+——-+————-+————+

    I believe denormalized schema is causing your problems here. Any time you have a column that means more than one thing depending on the value of another column, you may have a troublesome design. You should probably redesign these tables. MySQL’s site has one of the best introductions to normalization I have seen.

  13. 13 Anonymous
    Nov 14th, 2006 at 11:43 am

    Thanks. found what I was looking for in your example. You saved my life today.

  14. 14 Brandon
    Nov 14th, 2006 at 7:03 pm 
    SELECT *
FROM TABLE tbl1
WHERE EXISTS (
  SELECT
    NULL
  FROM
    TABLE tbl2
  WHERE
    tbl2.ID1= tbl1.ID1
    AND tbl2.ID2= tbl1.ID2
  GROUP BY
    tbl2.ID1, tbl2.ID2
  HAVING
    tbl1.ID < MAX(tbl2.ID)
)
  15. 15 Brian Ganly
    Dec 14th, 2006 at 5:54 am

    Here is another approach:

    select distinct all the duplicate rows into another table,
    delete the rows from the original table,
    insert the rows from the distinct rows table back into the original table.

  16. 16 Clift Maples
    Dec 15th, 2006 at 11:00 am

    Thanks saved me a lot of time

  17. 17 khalil
    Dec 24th, 2006 at 12:06 pm

    Hi there, i got some problem here and i hope i got your attention. I’ve got two table, attendance and subject table.

    [ed: snipped tables out and re-worded some things for clarity]

    i want to compare both table at ‘metric_no’.
    And i want only those not duplicate result to show.

    basically what i want to accomplish here is to compare the ‘attendance’ table, data from date_range1 to date_range2 against table ’subject_table’.

    So, if there is duplicate, i want to remove it (just don’t show it, not completely remove from database).
    If there is another way to archive this please teach me.

  18. 18 Xaprb
    Dec 24th, 2006 at 3:33 pm

    Hi hkalil, your problem is about selecting non-matching rows, not removing duplicate rows. I use an exclusion join for this.

  19. 19 mwq27
    Jan 10th, 2007 at 7:01 pm

    Hi, I am having a little trouble understanding the query to find the duplicate entries. i have 1 table called Details with column headings ID and Date. I need to find when the ID and corresponding date are the same: for example, 

    ID        date
2      2005-01-01
2      2005-01-01

    So i need to see the ID and date when they are exactly the same. Im trying to find errors where a transaction goes thru twice on accident. PLease help thanks

  20. 20 Xaprb
    Jan 11th, 2007 at 9:25 am

    Hi mwq27, in your case you should define “duplicate” as “has the same pair of values in (ID, date)” (see the first sub-section in the article). Then you GROUP BY those columns together, and add a HAVING clause to see only rows that are repeated. For example:

    select ID, date, count(*)
from yourtable
GROUP BY ID, date
HAVING count(*) > 1

    I hope that helps!

  21. 21 pk340
    Jan 19th, 2007 at 10:25 am

    hi ,

    I have doubt imagine a scenario where my table doesn’t have a Primary key in that case how do I:

    1. Display only the duplicate records

    2. Delete these records only.

  22. 22 Xaprb
    Jan 19th, 2007 at 3:24 pm

    Lack of a primary key ALWAYS makes it harder. You can display the duplicates easily with the instructions in this post. To delete them, follow the instructions in the next post (there is a link on this page) but you will have to use a LIMIT clause to avoid deleting every row that’s duplicated (I assume you only want to delete some of them, so you keep your data but just delete the duplicate rows).

  23. 23 Brian Ganly
    Feb 27th, 2007 at 12:28 pm

    My last example had a lot of assumed knowledge. Here is a worked example from a real life situation.

    d system_size
                  Table "system_size"
 Attribute  |     Type      | Modifier | Default Value
------------+---------------+----------+---------------
 date_stamp | date          | not null |
 db_name    | character(32) | not null |
 table_name | character(32) | not null |
 byte_size  | numeric(18,0) | not null |

    nb pk should be first 3 columns but DB does not enforce pk constraints

    --Find days with duplicates, e.g. days which have about double the number of rows of other days.
SELECT DATE_STAMP,
       COUNT(* )
FROM     SYSTEM_SIZE
GROUP BY DATE_STAMP
ORDER BY 1;

--Create backup
CREATE TABLE SYSTEM_SIZE_BAK
 AS
  SELECT *
  FROM   SYSTEM_SIZE;

--Take out rows of duplicate day into temp table for days with duplicates
CREATE TABLE BB1
 AS
  SELECT DISTINCT *
  FROM   SYSTEM_SIZE
  WHERE  DATE_STAMP IN ('2007-02-05','2007-02-06');

--Find duplicates based on logical pk, e.g same pk different bytes values
SELECT *
FROM   BB1
WHERE  (DATE_STAMP,DB_NAME,TABLE_NAME) IN (SELECT   DATE_STAMP,
                                                    DB_NAME,
                                                    TABLE_NAME
                                           FROM     BB1
                                           GROUP BY DATE_STAMP,DB_NAME,TABLE_NAME
                                           HAVING   COUNT(* ) > 1);

--Show rows rows to delete
SELECT *
FROM     BB1
WHERE    (DATE_STAMP,DB_NAME,TABLE_NAME,BYTE_SIZE) IN (SELECT   DATE_STAMP,
                                                                DB_NAME,
                                                                TABLE_NAME,
                                                                MIN(BYTE_SIZE)
                                                       FROM     BB1
                                                       GROUP BY DATE_STAMP,DB_NAME,TABLE_NAME
                                                       HAVING   COUNT(* ) > 1)
ORDER BY 1,2,3;

--delete duplicate rows, if byte_size different made decision to keep the highest byte value
DELETE FROM BB1
WHERE       (DATE_STAMP,DB_NAME,TABLE_NAME,BYTE_SIZE) IN (SELECT   DATE_STAMP,
                                                                   DB_NAME,
                                                                   TABLE_NAME,
                                                                   MIN(BYTE_SIZE)
                                                          FROM     BB1
                                                          GROUP BY DATE_STAMP,DB_NAME,TABLE_NAME
                                                          HAVING   COUNT(* ) > 1)

--check all is there
SELECT DATE_STAMP,
       COUNT(* )
FROM     BB1
GROUP BY DATE_STAMP
ORDER BY 1;

--Truncate original table and insert back rows that should be there from backup and deduped table
TRUNCATE TABLE SYSTEM_SIZE;

INSERT INTO SYSTEM_SIZE
SELECT *
FROM   SYSTEM_SIZE_BAK
WHERE  DATE_STAMP NOT IN ('2007-02-05','2007-02-06');

INSERT INTO SYSTEM_SIZE
SELECT *
FROM   BB1;

--Find days with duplicates, check again
SELECT DATE_STAMP,
       COUNT(* )
FROM     SYSTEM_SIZE
GROUP BY DATE_STAMP
ORDER BY 1;
  24. 24 Kunal
    Mar 1st, 2007 at 2:36 am

    Nice Tutorial

  25. 25 LamKaran
    Mar 4th, 2007 at 7:19 pm

    I have a table with 3 columns [k1(primary key),k2,k3).
    There are some rows that although they have different value in k1 they have identical k2,k3 columns with some other rows above.I want to delete these duplicate rows.
    Using (select distinct k2,k3) i get the rows that i want to keep but i cant find a way to delete the remaining rows.
    Any help is welcome
    thanks

  26. 26 Xaprb
    Mar 4th, 2007 at 8:39 pm

    Please see my later posts on deleting duplicate rows.

  27. 27 Matt
    Mar 6th, 2007 at 4:02 pm

    Very useful. Thanks very much for sharing your knowledge.

  28. 28 Devkant
    Mar 14th, 2007 at 2:15 am

    It helps me lot. Thanks.

  29. 29 Ekin
    Mar 16th, 2007 at 5:18 pm

    Xaprb…I have a question..using your above example of finding the duplicate rows showing the “day” and the “count”….

    select day, count(*) from test group by day HAVING count(*) < 1;
+------------+----------+
| day        | count(*) |
+------------+----------+
| 2006-10-08 |        2 |
+------------+----------+

    Is there a way to show all the fields that are duplicates just like below? I want the ID, day field to show…is it possible ??

    +----+------------+
| id | day        |
+----+------------+
|  1 | 2006-10-08 |
|  2 | 2006-10-08 |
+----+------------+
  30. 30 Xaprb
    Mar 17th, 2007 at 8:21 am

    I think the queries I showed in my post on first/least/max row per group will help you.

  31. 31 Martin
    Apr 13th, 2007 at 10:05 am

    Hi

    Hopefully someone can help with this little problem I’m having. I’m removing duplicate rows from a table by grouping five VARCHAR columns together and then using count(*) > 1. This is working fine except for when there are NULL values involved.

    For example, if column C is NULL in rows 2 and 3 and columns A, B, D and E are contain identical strings, I want this grouping of values to be marked as a duplicate. But this is not happening. MYSQL doesn’t seem to be seeing that NULL is the same as NULL. If I change the NULL values in rows 2 and 3 to a space instead, the process works fine and the grouping is returned as a duplicate.

    Hopefully that makes sense. Thank you for any help.

  32. 32 Xaprb
    Apr 13th, 2007 at 10:09 am

    Your trouble probably stems from misunderstanding NULL comparisons, which also affect COUNT(). I wrote an article about why NULL never compares false to anything in SQL. That might help you understand what is happening.

  33. 33 sud desai
    Apr 16th, 2007 at 11:44 am

    Hi

    i got a delivery events table, where parcels were failed or delivered and other event logs, the problem i have is a parcel could be delivered twice on the same day but different times, this is due to the driver input on his hand held machine, or other times where the system does this. i have a table where the columns for time and date are different and just what the records of parcels that had the first log on the same day and not the second log. any help would be great

    cheers suds

  34. 34 Joe Schmoe
    May 2nd, 2007 at 11:26 am

    This has been extremely helpful. Thank you for taking the time to explain this in such detail.

  35. 35 Scott
    May 3rd, 2007 at 11:26 am

    I really enjoyed this page, I found it looking for the answer to this question:
    “Why do you have to use a HAVING clause, why can’t you use a WHERE clause?”

    I think I understand your answer … the order is important, but what if you put the where clause before the group by clause, in the correct order? For example, this would seem to be logically correct:

    Select state, count(state) from customers
    where count(state) > 1
    group by state;

    But it doesn’t work. However, this does:

    Select state, count(state) from customers
    group by state
    having count(state) > 1;

    Why?

  36. 36 Xaprb
    May 3rd, 2007 at 5:20 pm

    Hi Scott,

    It works because the clauses are applied at different points. WHERE filters rows before they are grouped, HAVING filters them after. COUNT() is applied over a group, so there is no COUNT() before the rows are grouped. That’s why you can’t refer to a grouped expression in the WHERE clause — the rows haven’t been grouped yet.

  37. 37 David Eriksson
    May 22nd, 2007 at 9:15 am

    Thanks for writing this blog post!

  38. 38 axt
    Aug 3rd, 2007 at 7:59 pm

    Hi this is a very useful blog. Thanks for posting. I have a question about developing a query to identify the duplicate records. I have built a view with the following fields: Site ID, Site Name, Address Line 1, City, State, Industry. Records are duplicates if more than one Site Name and City are exactly the same.

    Using the info in other posts I can find the duplicates if i only select the Site Name and City in for the report. But I also have to include all other fields as well (Site ID, Site Name, Address Line 1, City, State, Industry).

    How can use the group by and have all the fields in my report? Thanks in advance for your help.

  39. 39 Xaprb
    Aug 3rd, 2007 at 8:03 pm

    You need to create a set of duplicate Site_Name and City values, and then join that back to the entire data set. The easiest way is with a subquery in the FROM clause:

    select whole_thing.*
from whole_thing
inner join (
   ... your duplicate-finding query here ...
) as dupes using(Site_Name, City);
  40. 40 axt
    Aug 7th, 2007 at 3:05 pm

    Thanks Xaprb, you’re a genius. I couldn’t figure out the syntax for the inner join duplicate finding query in SQL Server so I created a new view for the duplicate finding query and the did the inner join and it works like a charm.

  41. 41 Imthiyas
    Sep 11th, 2007 at 6:41 am

    hi.. This is very useful blog.. i findout very useful query.. i need query for finding out duplicate rows from multiple tables…

  42. 42 nan
    Sep 22nd, 2007 at 1:50 am

    Hi, I foound this is a very nice site for sql. I have a small query. In my table there are three fields CustNo, IdNo, Cust Desc. There can be any number IdNo for the CustNo. But the IdNo assigned to one customer can not be assigned to another. Now which is the best query to check if there are any IdNo common to more than one customer. Here CustNo can be repreated and IdNo Can be also be repeated any number of times in the table.. Thanks..

  43. 43 Xaprb
    Sep 22nd, 2007 at 7:50 am

    Looks like you found the right place! It’s all on this page.

  44. 44 smith
    Sep 24th, 2007 at 7:25 am

    Hi all sql guru, please help me with the following:

    Based from the data below, I need to get all the LAST transaction per compound keys(SPID NPA NXX Line) and status != ‘Delete’

    Date SPID NPA NXX Line Status
    9-23-2007 08088 204 987 9531 Delete
    9-23-2007 08088 204 987 9532 Input
    9-23-2007 08088 204 987 9533 Delete
    9-23-2007 08088 204 987 9533 Add
    9-23-2007 08089 204 977 2954 Add
    9-23-2007 08089 204 977 2954 Delete
    9-23-2007 08089 204 977 2954 Add
    9-23-2007 08090 204 977 2956 Add
    9-23-2007 08090 204 977 2956 Delete

    Target Result:
    9-23-2007 08088 204 987 9532 Input
    9-23-2007 08088 204 987 9533 Add
    9-23-2007 08089 204 977 2954 Add

  45. 45 nan
    Sep 25th, 2007 at 2:16 am

    I am still confused . My table is like this ….

    IdNo CustId CustDesc
    1 C1 Customer1
    2 C1 Customer1
    3 C1 Customer1
    4 C2 Customer2
    5 C2 Customer2
    6 C2 Customer2
    2 C2 Customer2
    3 C3 CuStomer3
    7 C3 Customer3

    In the above example given I would like to see all the IdNos with different customers. My result will be IdNo’s 2 and 3. Thanks

  46. 46 Nancy
    Oct 5th, 2007 at 10:36 am

    I have 1 table I need to find all duplicates from 3 columns (address1 city state) so I created a view and concatenated those 3 columns into 1 new called address3. Each time I run a simple select distinct address3 query, I get a different result. Is there a better way to find and delete these duplicates? Thank you.

  47. 47 Moon
    Oct 17th, 2007 at 11:49 pm

    Thanks its a great article……..

    i used this query

    ” select b, c, count(*) from a_b_c group by b, c having count(1) or count(1); “

    it worked well

    but my the problem is that its also showing the null values too as duplicates ….. IS there any way to avoide this…….?

    thanks

  48. 48 Xaprb
    Oct 18th, 2007 at 6:23 am

    A WHERE clause should fix that. Remember col = NULL is always false.

  49. 49 moon
    Oct 22nd, 2007 at 2:51 am

    Xaprb………….thanks 4 the reply…….

    can u give an example ……….

    thanks

  50. 50 Donovan
    Nov 15th, 2007 at 8:14 pm

    A helpful way to get the index without too much trouble is to use MAX()…

    select max(article_id) from articles group by headline HAVING count(*) > 1

  51. 51 Bebop
    Mar 27th, 2008 at 7:03 am

    Does anyone want to help me?

    I’m new to SQL, an absolute beginner. I can do what I want to do in basic, but can’t access the databases that way!

    Anyway, I want to isolate the dublicates across two tables.
    The entries all have a unique ID, but I need a combination of the two to get what I need (All transactions, I need both ins and outs if you will!) Whatever it is I’ve done, every transaction is repeating 16 times (Why 16? - I have no idea either!) So if anyone reading this knows how to do the above but across two tables, please let me know and I’ll be your friend for life!

  1. 1 Deleting duplicate rows when there is no primary key « Welcome……..
    Pingback on Apr 30th, 2007 at 9:25 am
  2. 2 .bootstrap » MySQL: Remove Duplicate Entries
    Pingback on Jun 9th, 2007 at 6:00 am

Leave a Reply

Please do not use this blog to get help with problems or bugs in Maatkit or innotop: use the Sourceforge forums, mailing list, or bug trackers. If you're asking for help with MySQL, please use the MySQL mailing list instead. I'm writing a book and my time is extremely limited :-)

« Five great Perl programming techniques to make your life fun again
How to delete duplicate rows with SQL »