Find duplicate values in a field results in multiple rows of the same values-CodePudding

Suppose this table

I want to get all CODEs where SIZEPOS has the same value more than once...

So for the product

('194342-01', 10510, 5),
('194342-01', 10510, 7),
('194342-01', 10510, 9),
('194342-01', 10510, 11),
('194342-01', 10510, 13),
('194342-01', 10510, 15),
('194342-01', 10510, 5),
('194342-01', 10510, 19),

I would like to get '194342-01', 10510, 5 as SIZEPOS 5 appears twice. I achieved it with the following query

SELECT CODE, ITEID, COUNT(SIZEPOS) AS CNT
FROM @SUBSTITUTE
GROUP BY CODE, ITEID, SIZEPOS
HAVING COUNT(SIZEPOS) > 1;

but for some reason some of the returned rows appear twice, or even three times... What am I missing here?

CodePudding user response：

Row_number function is more reliable when identifying duplicates. Try apply row_number function in the sub query and filter unique records see below example.

DECLARE @SUBSTITUTE TABLE (CODE varchar(50), ITEID int, SIZEPOS int);

INSERT @SUBSTITUTE (CODE, ITEID, SIZEPOS) VALUES
('194342-01', 10510, 5),
('194342-01', 10510, 7),
('194342-01', 10510, 9),
('194342-01', 10510, 11),
('194342-01', 10510, 13),
('194342-01', 10510, 15),
('194342-01', 10510, 5),
('194342-01', 10510, 19),
('749747-010', 5415, 14),
('749747-010', 5415, 1),
('749747-010', 5415, 2),
('749747-010', 5415, 21),
('749747-010', 5415, 21),
('749747-010', 5415, 3),
('749747-010', 5415, 5),
('749747-010', 5415, 6),
('749747-010', 5415, 7),
('749747-010', 5415, 9),
('749747-010', 5415, 10),
('749747-010', 5415, 11),
('749747-010', 5415, 12),
('749747-010', 5415, 13),
('749747-010', 5415, 14),
('749747-010', 5415, 15),
( '749747-010', 5415, 22),
('749747-010', 5415, 24),
('935088-02', 10885, 3),
('935088-02', 10885, 5),
('935088-02', 10885, 4),
('935088-02', 10885, 5),
('935088-02', 10885, 5),
('935088-02', 10885, 6),
('935088-02', 10885, 7),
('935088-05', 10957, 4),
('935088-05', 10957, 4),
('935088-05', 10957, 5),
('935088-05', 10957, 5),
('935088-05', 10957, 6),
('935088-05', 10957, 7),
('935088-05', 10957, 7),
('AT1801-406', 6161, 3),
('AT1801-406', 6161, 4),
('AT1801-406', 6161, 6),
('AT1801-406', 6161, 7),
('AT1801-406', 6161, 9),
('AT1801-406', 6161, 10),
('AT1801-406', 6161, 11),
('AT1801-406', 6161, 13),
('AT1801-406', 6161, 14),
('AT1801-406', 6161, 15),
('AT1801-406', 6161, 17),
('AT1801-406', 6161, 3),
('H68096', 11397, 11),
('H68096', 11397, 2),
('H68096', 11397, 4),
('H68096', 11397, 12),
('H68096', 11397, 1),
('H68096', 11397, 8),
('H68096', 11397, 6),
('H68096', 11397, 1),
('H68096', 11397, 14),
('H68096', 11397, 7),
('H68096', 11397, 10),
('H68096', 11397, 9),
('M20-73354-10', 5395, 2),
('M20-73354-10', 5395, 3),
('M20-73354-10', 5395, 5),
('M20-73354-10', 5395, 6),
('M20-73354-10', 5395, 10),
('M20-73354-10', 5395, 11),
('M20-73354-10', 5395, 4),
('M20-73354-10', 5395, 5);

select * from (
SELECT CODE, ITEID, SIZEPOS, row_number() over (partition by 
code,iteid,sizepos order by code)cnt
FROM @SUBSTITUTE) a

where cnt=1

CodePudding user response：

I though this is what you expect to see (attached image) grouping by will not let you get '194342-01', 10510, 5 without using a distinct

enter image description here