find duplicates based on two fields

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 2005 Forums

Transact-SQL (2005)

find duplicates based on two fields

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-05-25 : 03:26:31

Hi

I need to find duplicates in a table based on 2 columns concantenated (col1 + col2), and also delete the duplicates, is that possible?

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-25 : 03:43:21

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

In Love... With Me!

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-05-25 : 06:44:26

CREATE TABLE [dbo].[tbl_Test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Col1] [nvarchar](255) NULL,
[Col2] [nvarchar](255) NULL,
[Col3] [nvarchar](50) NULL,
CONSTRAINT [PK_tbl_Test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','2','3')
INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','2','4')
INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','2','5')
INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('8','2','6')
INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','9','7')

I then need to find the first 3 rows as duplicates, although the rows in this example is simple. In real scenario col1 & col2 makes a unique combination and thats what I need to look for.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-25 : 07:09:41

-- show duplicates
select * from
(select
row_number() over(partition by Col1,Col2 order by ID) as rownum,
*
from tbl_test)dt
where rownum > 1

-- delete duplicates
delete dt
from
(select
row_number() over(partition by Col1,Col2 order by ID) as rownum,
*
from tbl_test)dt
where rownum > 1

No, you're never too old to Yak'n'Roll if you're too young to die.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-25 : 07:16:19

Try this

CREATE TABLE [dbo].[tbl_Test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Col1] [nvarchar](255) NULL,
[Col2] [nvarchar](255) NULL,
[Col3] [nvarchar](50) NULL,
CONSTRAINT [PK_tbl_Test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--
INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','2','3')
INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','2','4')
INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','2','5')
INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('8','2','6')
INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','9','7')
--INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','2','3')
--INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','2','4')
--INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','2','5')
--INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('8','2','6')
--INSERT INTO tbl_Test (Col1, Col2, Col3) VALUES ('1','9','7')

;With CTE as
(
Select *,row_number() over (partition by Col1,Col2 order by Col1,col2 Desc) as row1
from [tbl_Test])
Delete from CTE where row1!=1

Select * from [tbl_Test]

In Love... With Me!

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-05-25 : 07:18:50

I previously used this when I only checked one column...

SELECT Col1

, COUNT(Col1) nr

FROM tbl_Test

GROUP BY Col1

HAVING COUNT(Col1) > 1

@Webfred, I get an error on "row_number()" as not a regocnigsed function

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-25 : 07:23:17

Then you are not using SQL Server 2005 but this is a forum for 2005.

No, you're never too old to Yak'n'Roll if you're too young to die.

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-05-25 : 07:29:24

Sorry, my mistake. You wouldn't happend to know the simular check in sql 2000 or should I repost...

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-05-25 : 10:42:24