Dear Friends,
Could anyone let me know how to search for duplicate recorsd in the same table?
I have 20 attributes in the table and have the first 2 attributes
ID1,ID2-the combination of which is unique.
How to write a query to retrieve the duplicate records in the same table(It should compare the remaning 18 attributes and retrieve if they all match).Is this something which can be done in SQL?

The following will retrieve all records that have duplicate ID1 and ID2:

SELECT ID1, ID2, ..., COUNT(1) FROM table_name
GROUP BY ID1, ID2
HAVING COUNT(1) > 1;

N_I's query gives the count ,

If you want the actual rows, you can use a CTE using the same query as sample

with temp as
(
SELECT ID1, ID2, ..., COUNT(1) FROM table_name
GROUP BY ID1, ID2
HAVING COUNT(1) > 1
)
select * from table_name where
(id1,id2....) in (select id1,id2 .... from temp
)

HTH

Sathyaram

Thanks for the response....
But I dont have duplicate ID1,ID2...This combinaton is unique

Imagine the sample records

ID1 ID2 NAME SSN BIRTHDATE
100 1 MARK 999-99-999 1980-01-01
100 2 MARK 999-99-999 1980-01-01
200 2 CHRIS 888-88-8888 1990-01-01

I am looking for a query which compares the two records
(100,1) & (100,2) for the NAME,SSN,BIRTHDATE(LIKEWISE I have 20 other columns in this table) and gives the count of records if all the attributes match(apart from ID1,ID2)....Hope my question was clear

...
GROUP BY NAME, SSN, BIRTHDATE
...

Try this statement:

SELECT A.ID1, A.ID2
FROM TBL A
WHERE 1 < (SELECT COUNT(*) FROM TBL B
WHERE B.FLD1 = A.FLD1
AND B.FLD2 = A.FLD2
AND B.FLD3 = A.FLD3
AND ... )

Regards, ... Aloz