finding duplicates in a table

Question

Possible Duplicate:
SQL - How can I remove duplicate rows?

Hello all

How I can Write following query .

I have a table Trace and I want to get all lines there where ID columns and MC columns combinations are appear more then once.

for example all lines where ID = 2 and MC = 11 appear more then once .

Thanks

Asked many many times before. http://www.google.co.uk/search?sourceid=chrome&ie=UTF-8&q=site%3Astackoverflow.com+find+duplicates+database — Oded, May 18 '11 at 12:15

score 1 · Accepted Answer · answered May 18 '11 at 12:16

1

You could group on ID, MC, and use having to select combinations that occur more than once:

select  ID
,       MC
from    Trace
group by
        ID
,       MC
having  count(*) > 1

answered May 18 '11 at 12:16

Andomar

232,371
49
380
404

I want also to see a column callen TimeDone, how i can do it here ? – Night Walker May 18 '11 at 12:23
Add `, Min(TimeDone) AS MinTimeDone, Max(TimeDone) AS MaxTimeDone` to the select clause. – Tao May 18 '11 at 12:31

score 0 · Answer 2 · answered May 18 '11 at 12:15

0

SELECT *
FROM Trace T1
INNER JOIN (
  SELECT ID, MC
  FROM Trace T2
  GROUP BY ID, MC
  HAVING COUNT(*) > 1
) T22
ON T22.ID = T1.ID
AND T22.MC = T1.MC

answered May 18 '11 at 12:15

Will A

24,780
5
50
61

finding duplicates in a table

2 Answers2