Extract duplicates - 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 2008 Forums

Transact-SQL (2008)

Extract duplicates

Author

Topic

risshanth
Starting Member

1 Post

Posted - 2011-02-16 : 15:39:10

COLA COLB COLC

2051417 ABCW 3
2051733 ABCW 38
0969301 ABCW 4
0701500 ABCW 5
1990037 ABCW 5
0703632 ABCW 6
2051478 ABCW 7
0569059 ABCW 7

I need the output as follows:
0701500 ABCW 5
1990037 ABCW 5
2051478 ABCW 7
0569059 ABCW 7

Please help with the query

Thanks,
Risshanth

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2011-02-16 : 15:44:13

DECLARE @t TABLE (COLA VARCHAR(7), COLB CHAR(4),COLC INT);

INSERT @t VALUES
('2051417','ABCW','3'),
('2051733','ABCW','38'),
('0969301','ABCW','4'),
('0701500','ABCW','5'),
('1990037','ABCW','5'),
('0703632','ABCW','6'),
('2051478','ABCW','7'),
('0569059','ABCW','7');

WITH cte1
AS
(
SELECT t.*, COUNT(*) OVER(PARTITION BY COLB,COLC) AS DupCount
FROM @t t
)
SELECT *
FROM cte1 c
WHERE c.DupCount > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources