Displaying the duplicate entries in a table

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 Administration (2000)

Displaying the duplicate entries in a table

Author

Topic

sqldbaa
Starting Member

32 Posts

Posted - 2008-02-13 : 05:31:59

Hi,

Please let me know how to find out or display only the duplicate entries in table.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-13 : 05:44:08

one method :-

SELECT t1.* FROM table t1
LEFT OUTER JOIN (SELECT MIN(PK),duplicated other columns
                 FROM Table 
                 GROUP BY duplicated columns)t2
ON t1.PK=t2.PK
WHERE t2.PK IS NULL

PK is primary key

stevelund
Starting Member

5 Posts

Posted - 2008-02-13 : 05:52:01

You could do something like:


SELECT *
FROM [TableName]
WHERE [ColumnUsedToIdentifyDuplicates] IN
(SELECT [ColumnUsedToIdentifyDuplicates]
FROM [TableName]
GROUP BY [ColumnUsedToIdentifyDuplicates]
HAVING COUNT(*) > 1)
ORDER BY [ColumnUsedToIdentifyDuplicates]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-13 : 05:55:09

select t1.* from table t1 inner join
(select dupcol from table group by dupcol having count(*)>1) as t1
on t1.dupcol=t2.dupcol

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources