question

hari avatar image
hari asked

Identify Duplicated from for certain records only

There are **duplicate records** Find duplicate records **based on ExtNum**. But check that only for records where **findduplicate=** **'Y'** *EDIT (changed in sample to bit value)* DECLARE @TestTable TABLE (vid int, NAME varchar(20), extnum int, findduplicate bit) INSERT INTO @TestTable (vid, NAME, extnum, findduplicate) VALUES (1, 'hhh', 11111, 1), (2, 'bbb', 22222, 0), (3, 'ccc', 4444, 0), (4, 'hhh', 11111, 0), (5, 'bbb', 22222, 0) ; So the **result should return**
VID NAME ExtNum 1 hhh 11111 4 hhh 11111 **FYI:-**
Though there are duplicates on the ExtgNum=22222, it should **not come in the result** set We should get **only** "11111" related supplier list
delete-duplicates
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hari avatar image hari commented ·
CREATE TABLE Test ( sid int identity(1,1), sname varchar(255), extnum varchar(255), fd bit ) INSERT INTO Test(sname,extnum,fd) values('hhh',11111,1) INSERT INTO Test(sname,extnum,fd) values('bbb',22222,0) INSERT INTO Test(sname,extnum,fd) values('ccc',44444,1) INSERT INTO Test(sname,extnum,fd) values('hh',11111,0) INSERT INTO Test(sname,extnum,fd) values('bb',22222,0) SELECT *FROM Test WHERE ExtNum IN ( SELECT T.ExtNum FROM Test T INNER JOIN ( SELECT ExtNum FROM Test WHERE FD = 1) FDTrue
ON T.ExtNum = FDTrue.ExtNum Group By T.ExtNum Having Count(T.ExtNum) > 1) drop table Test
0 Likes 0 ·
hari avatar image hari commented ·
SELECT EXT_VENDOR_NUMBER + DATA_SOURCE AS EXT_DATA_SOURCE, V.VENDOR_ID, V.NAME FROM cvmsa.vendor v WHERE v.delete_Flag IS NULL AND v.ext_vendor_number + v.data_source IN (SELECT ext_vendor_number + data_source FROM cvmsa.vendor WHERE delete_flag IS NULL AND ext_vendor_number + data_source IN (SELECT ext_vendor_number + data_source FROM cvmsa.vendor WHERE delete_flag IS NULL AND SVU_Final_Scrub = 'SVU_Final_Scrub') GROUP BY ext_vendor_number + data_source HAVING COUNT(ext_vendor_number + data_source) > 1) ORDER BY ext_vendor_number + data_source, v.SVU_Final_Scrub
0 Likes 0 ·

2 Answers

·
Matt Whitfield avatar image
Matt Whitfield answered
SELECT * FROM unspecified_table_name WHERE EXTNUM IN (SELECT EXTNUM FROM unspecified_table_name GROUP BY EXTNUM HAVING COUNT(*) > 1 INTERSECT SELECT EXTNUM FROM unspecified_table_name WHERE FINDDUPLICATE = 1) I would have tested this for you too, if you had posted your table def and insert statements to specify your test case.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
I created the test data (and have appended it to your question DECLARE @TestTable TABLE (vid int, NAME varchar(20), extnum int, findduplicate bit) INSERT INTO @TestTable (vid, NAME, extnum, findduplicate) VALUES (1, 'hhh', 11111, 1), (2, 'bbb', 22222, 0), (3, 'ccc', 4444, 0), (4, 'hhh', 11111, 0), (5, 'bbb', 22222, 0) ; WITH DupList AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY extnum ORDER BY (SELECT NULL)) rn FROM @testtable TT WHERE EXISTS ( SELECT * FROM @TestTable TT2 WHERE TT2.extnum = TT.extnum AND findduplicate = 1 )) SELECT * FROM DupList -- WHERE rn>1 -- Only return the entries that are duplicates /* Delete the Duplicates */ --DELETE --FROM DupList --WHERE rn>1
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
The exec plan is nicer here, but it just finds all rows where there is a matching extnum with findduplicate = 1 - whether there are duplicates or not... +1 for recreating the test data though :)
0 Likes 0 ·
KenJ avatar image KenJ commented ·
Combine the answers to only show dupe rows and still provide a nice mechanism for deleting them? WITH DupList AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY extnum ORDER BY (SELECT NULL)) rn FROM @testtable TT WHERE EXISTS ( SELECT EXTNUM FROM @TestTable WHERE extnum = tt.extnum GROUP BY EXTNUM HAVING COUNT(*) > 1 INTERSECT SELECT EXTNUM FROM @TestTable WHERE extnum = tt.extnum AND FINDDUPLICATE = 1)) SELECT * FROM DupList; :)
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
@Matt - the where clause that I commented out returns the ones that are duplicates, so it works - oramiwrong?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.