Search for duplicate rows

Hy,

Could anyone please help me.
I have a table with thousands of rows. I would like to make a query to retrieve only duplicate rows in the table. The query should return rows having 2 columns id1 and id2. id1 and id2 are from duplicate rows in the table. The row should list id1 and id2 only once. How can I implement this query.

thanks in advance

SELECT id1, id2, COUNT(id1)
FROM Table1
GROUP BY id1, id2

This will show how many times each row was duplicated.

I did what you told me, unfortunately this does not work.

DO you want to bring back all duplicate records, or just know which records have duplicates?

S-

bring back all duplicate records

What this is using a SELECT Statment the dcarva gave you, I am joining it back to the orginal table to give you a listing of all Duplicate records.

Use this as a template. You will have to specify which fields you want to see and which field make a record duplicate.

In this example I have two fields that make a record duplicate and that I want to see when it is all said and done


SELECT Table2.FieldOne, Table2.FieldTwo
FROM Table2 INNER JOIN [SELECT Table2.FieldOne, Table2.FieldTwo, Count(Table2.FieldOne) AS CountOfFieldOne
FROM Table2
GROUP BY Table2.FieldOne, Table2.FieldTwo
HAVING (((Count(Table2.FieldOne))>1))]. AS PLAY_GROUP
ON ( [PLAY_GROUP].[FieldOne]=[Table2].[FieldOne])
AND ( [PLAY_GROUP].[FieldTwo]=[Table2].[FieldTwo]);

Orginal Data
FieldOne | FieldTwo|
45 | 78
45 | 78
98 | 78
98 | 78
45 | 78

Statement brings back
FieldOne | FieldTwo|
45 | 78
45 | 78
98 | 78
98 | 78

Hope this helps
S-