asked
Syntax Assistance
CREATE TABLE HLCC ( DocumentType VARCHAR(3), DocumentNumber VARCHAR(8), ItemCode VARCHAR(50), CustomerCode VARCHAR(50)) INSERT INTO HLCC VALUES('8','PN305334','CON/OVERALL','LOO001') INSERT INTO HLCC VALUES('8','PN305334','CON/OVERALL','LOO001') INSERT INTO HLCC VALUES('8','PN305334','CON/OVERALL','LOO001') INSERT INTO HLCC VALUES('8','PN305334','0290000','LOO001') INSERT INTO HLCC VALUES('8','PN305335','CON/OVERALL','LOO001') INSERT INTO HLCC VALUES('8','PN305335','CON/OVERALL','LOO001') INSERT INTO HLCC VALUES('8','PN305335','CON/OVERALL','LOO001') INSERT INTO HLCC VALUES('8','PN305335','0290000','LOO001') INSERT INTO HLCC VALUES('8','PN305350','PVC/APR/BF','LOO001') INSERT INTO HLCC VALUES('8','PN305350','PVC/APR/BF','LOO001') INSERT INTO HLCC VALUES('8','PN305350','PVC/APR/BF','LOO001') INSERT INTO HLCC VALUES('8','PN305350','0290000','LOO001') SELECT * FROM HLCC ORDER BY DocumentNumber
Each document number has a different document type, supplier invoices have the 8 shown here, and for each document number, there is only supposed to be one unique item code per document. If you take a look here, document PN305350 has PVC/APR/BF in 3 times and 0290000 in once. I need some form of syntax where I can eliminate the duplicate itemcodes per document number, per document type, per customer code so that only unique values remain. Any help will be greatly appreciated. Thanks
Assuming that you need to delete every appearance of the duplicate ItemCode for each DocumentType, DocumentNumber, CustomerCode combination, meaning that in the example of document PN305350 you want to delete 2 out of 3 PVC/APR/BF and retain only PVC/APR/BF once and 0290000 once, you can do something like this: ;with dups as ( select *, row_number() over ( partition by DocumentType, DocumentNumber, ItemCode, CustomerCode order by DocumentType, DocumentNumber, ItemCode) N from HLCC ) delete from dups where N > 1; If you want to see how it works, just execute the statement inside of the CTE definition to see how the duplicates are revealed. With your sample data, the following rows remain after the duplicates are deleted: select * from HLCC DocumentType DocumentNumber ItemCode CustomerCode ------------ -------------- ------------- ------------ 8 PN305334 CON/OVERALL LOO001 8 PN305334 0290000 LOO001 8 PN305335 CON/OVERALL LOO001 8 PN305335 0290000 LOO001 8 PN305350 PVC/APR/BF LOO001 8 PN305350 0290000 LOO001 Hope this helps. Oleg
15 People are following this question.
