CODEWITHSUNDEEP
sql-servert-sqlsql-server-2016
boogie woogie
boogie woogie

Reputation: 13

Check which all records have duplicate using window function

I am using ROW_NUMBER() to find duplicates, encapsulated under CTEs. I am using ROW_NUMBER() because I also want to have column which shows how many duplicate rows are present in the table.

The below code gives only records greater than 1. That is row number 2 and 3. But how can I include row number 1 of duplicate records?

If I removed T>1, than output also contain records which does not have duplicate like records with Part "'0020R5',100".

DDL:

DROP TABLE #TEST
CREATE TABLE #TEST
(
    PART VARCHAR(30),   
    ALTPART int
)

INSERT #TEST    
SELECT '15-AB78',100 UNION ALL
SELECT '15-AB78',110 UNION ALL
SELECT '16-A9-1',100 UNION ALL
SELECT '16-A9-1',110 UNION ALL
SELECT '16-B97-2',100 UNION ALL
SELECT '16-B97-2',110 UNION ALL
SELECT '0020R5',100

Query:

WITH TEST(PART,ALTPART,T) AS   
(  
    SELECT PART,ALTPART,ROW_NUMBER() OVER (PARTITION BY PART ORDER BY ALTPART ASC) AS T FROM #TEST   
)  
SELECT PART,ALTPART,T 
FROM TEST  
WHERE T>1
ORDER BY PART
GO

Current output:
 '15-AB78',110,2 
 '16-A9-1',110,2 
 '16-B97-2',110,2

Expected Result:

'15-AB78',100,1
'15-AB78',110,2 
'16-A9-1',100,1 
'16-A9-1',110,2 
'16-B97-2',100,1 
'16-B97-2',110,2

Upvotes: 0

Views: 2113

Answers (1)

Alex
Alex

Reputation: 5157

You need to add another window function to count the number of duplicates in each group.

Something along these lines:

WITH TEST(PART,ALTPART,DuplicateNum, DuplicateCnt) AS   
(  
    SELECT PART,ALTPART,
        -- Number each duplicate
        ROW_NUMBER() OVER (PARTITION BY PART ORDER BY ALTPART ASC) AS DuplicateNum,
        -- Count duplicates
        COUNT() OVER (PARTITION BY PART ) AS DuplicateCnt,
    FROM #TEST   
)  
SELECT PART, ALTPART, DuplicateNum
FROM TEST  
WHERE DuplicateCnt > 1
ORDER BY PART

Upvotes: 1

Related Questions