Listing duplicate fields in 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

Transact-SQL (2000)

Listing duplicate fields in table

Author

Topic

spib
Starting Member

9 Posts

Posted - 2002-08-31 : 19:43:18

Hi,

I've seen a few articles in here about deleting duplicates etc but what I need to do is list all my duplicate records. They will only be assessed on whether they are duplicates on two columns out of ten (the other 8 may well contain different data).

Any help much appreciated

James

r937
Posting Yak Master

112 Posts

Posted - 2002-08-31 : 21:25:43

i'm not sure i understand the problem

does this give you what you want?

select col1, col2, ... , col10
from yourtable
where col1 || col2
in ( select col1 || col2
from yourtable
group by col1, col2
having count(*) > 1 )

rudy
http://rudy.ca/

spib
Starting Member

9 Posts

Posted - 2002-09-01 : 04:59:17

Sorry if I wasn't clear. What I need to show is all the duplicate records so we get this sort of thing

COL1   COL2    COL3
------------------------
foo      bar      bun
foo      bar      sun
foo      mar      fun
foo      mar      run
moo     cow      bun
moo     cow      sun

The first two fields are used to decide if the record is a duplicate.

BTW I ran your code but SQLServer choked on the '||' sign.

r937
Posting Yak Master

112 Posts

Posted - 2002-09-01 : 05:02:09

replace the || with a plus sign (concatenation) -- i originally had a plus sign but the forum software kept hiding it

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-09-01 : 05:08:50

Here is an example:

CREATE TABLE Dups
(
i int,
j char,
k datetime
)
GO

CREATE CLUSTERED INDEX myIdx ON Dups(i, j)
GO

INSERT Dups SELECT 1, 'A', GETDATE()
INSERT Dups SELECT 1, 'A', GETDATE()
INSERT Dups SELECT 2, 'B', GETDATE()
INSERT Dups SELECT 3, 'C', GETDATE()
INSERT Dups SELECT 3, 'C', GETDATE()
INSERT Dups SELECT 4, 'D', GETDATE()
INSERT Dups SELECT 5, 'E', GETDATE()
INSERT Dups SELECT 5, 'E', GETDATE()
GO

SELECT d1.i, d1.j, d1.k
FROM Dups d1 JOIN
(
SELECT i, j
FROM Dups
GROUP BY i, j
HAVING COUNT(*) > 1
) AS d2
ON d1.i = d2.i AND d1.j = d2.j

--
HTH,
Vyas
http://vyaskn.tripod.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources