Find duplicates for several columns exclusive ID-column

Question

i've found a lot of answers on how to find duplicates including the PK-column or without focus on it as this:

If you have a table called T1, and the columns are c1, c2 and c3 then this query would show you the duplicate values.

SELECT C1, C2, C3, count(*)as DupCount
 from T1
 GROUP BY C1, C2, C3
 HAVING COUNT(*) > 1

But a more common requirement would be to get the ID of the all duplicates that have equal c1,c2,c3 values.

So i need following what doesn't work because the id must be aggregated:

SELECT ID
 from T1
 GROUP BY C1, C2, C3
 HAVING COUNT(*) <> 1

(The ID of all duplicates must be different but the columns must be equal)

Edit:

Thank you all. I'm always suprised how fast people give excellent answers on Stackoverflow!

can you provide some sample data? – Nathan Feger Feb 18 '11 at 15:31 — Nathan Feger, Feb 18 '11 at 15:31

score 7 · Accepted Answer · answered Feb 18 '11 at 15:55

7

There is a lot of versions suggested here but I think I came up with a new one.

select *
from @T as T1
where exists (select *
              from @T as T2
              where
                T1.ID <> T2.ID and
                T1.C1 = T2.C1 and
                T1.C2 = T2.C2 and
                T1.C3 = T2.C3)

answered Feb 18 '11 at 15:55

Mikael Eriksson

136,425
22
210
281

Your approach needs only 11 seconds as against the recursive cte's that need 25 seconds. – Tim Schmelter Feb 18 '11 at 16:02
@Tim - They aren't recursive CTEs. They are CTEs using windowed aggregate functions. @Mikael +1 Checking for existence is all that is needed here. – Martin Smith Feb 18 '11 at 16:12

score 3 · Answer 2 · answered Feb 18 '11 at 15:34

3

;WITH CTE
     AS (SELECT ID,
                C1,
                C2,
                C3,
                COUNT(*) OVER (PARTITION BY C1, C2, C3) AS Cnt
         FROM   T1)
SELECT ID,
       C1,
       C2,
       C3
FROM   CTE
WHERE  Cnt > 1

answered Feb 18 '11 at 15:34

Martin Smith

438,706
87
741
845

This answer presumes sql server >= 2005 ? – Nathan Feger Feb 18 '11 at 15:35
@Nathan - Yep. It's 2011 now if the OP needs a 2000 compatible solution they should state it in the question! – Martin Smith Feb 18 '11 at 15:39
@Martin certainly, but did I miss sqlserver in the question, or can that be assumed since this is SO. :) – Nathan Feger Feb 18 '11 at 15:42
Thank you very much, only 25 seconds. I canceled my first approach after 45 minutes ;) – Tim Schmelter Feb 18 '11 at 15:53

score 3 · Answer 3 · answered Feb 18 '11 at 15:35

To get all the rows that are duplicates:

Use this:

WITH Dups AS
(
    SELECT *, 
           COUNT(1) OVER(PARTITION BY C1, C2, C3) AS CNT
      FROM T1  
)
SELECT * 
  FROM Dups
 WHERE CNT > 1

and to unique row (i.e. retain one row and filter the other duplicate rows) use this:

WITH NoDups AS
(
    SELECT *, 
         ROW_NUMBER() OVER(PARTITION BY C1, C2, C3 ORDER BY ID) AS RN
      FROM T1  
)
SELECT * 
  FROM NoDups
WHERE RN = 1

score 1 · Answer 4 · answered Feb 18 '11 at 15:35

Assuming at least SQL 2005 for the CTE:

;with cteDuplicates as (
    select c1, c2, c3
        from t1
        group by c1, c2, c3
        having count(*) > 1
)
select id
    from t1
        inner join cteDuplicates d
            on t1.c1 = d.c1
                and t1.c2 = d.c2
                and t1.c3 = d.c3

score 0 · Answer 5 · answered Feb 18 '11 at 15:34

0

I don't totally understand your problem, but here is a shot at a different style of solution:

select id
from t1 a
join t1 b on a.c1 = b.c2
join t1 c on b.c2 = c.c3
where a.id <> b.id and b.id <> c.id and a.id <> c.id

answered Feb 18 '11 at 15:34

Nathan Feger

19,122
11
62
71

score 0 · Answer 6 · answered Feb 18 '11 at 15:35

You can store the C1, C2, C3 combination for duplicates into a temp table and then join it to get the IDs.

select C1, C2, C3
into #duplicates
from T1
group by C1, C2, C3
having count(*) > 1

select ID
from T1 t
inner join #duplicates d
    on  t.C1 = d.C1
    and t.C2 = d.C2
    and t.C3 = d.C3

Find duplicates for several columns exclusive ID-column

6 Answers6

Linked