how to find duplicate records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to find duplicate records

Author

Topic

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-06-16 : 00:01:24

hi all,
i have a table that contains duplicate records for an id
can anybody help me to write a query to find duplicate records

thanks in advance

durgeshcool

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-16 : 00:08:07

select id, (count(*) - 1) as Duplicates from table group by id having count(*) > 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 02:47:48

SELECT IDENTITY(int,1,1) AS Seq,
ID,
Otherfields...
INTO #Temp
FROM YourTable

SELECT t1.*
FROM #Temp t1
LEFT JOIN (SELECT ID,MIN(Seq) AS MinRec
           FROM #Temp
           GROUP BY ID)t2
ON t2.ID=t1.ID
AND t2.MinRec=t1.Seq
WHERE t2.ID IS NULL
AND t2.MinRec IS NULL
ORDER BY t1.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources