Select rows which have column value occurring more than One in SQL Server

Last Reply 5 months ago By abhi619

Posted 6 months ago

How to get rows who have one column value occurring more than 1 in SQL table

I have a table A and 4 column a,b,c,d.

a,b,c may have same data but column d is different.

I want to find out of those rows where d value occurs more than 1 for same a,b,c data.

Product Variant Part Order
ab vb a1 1
ab vb a2 1
ab vb a3 2
ab vb a4 3
ab vb a5 4

Like above I want 1st and 2nd row as result because they have different Part but both have same order.

So, I want to search those entries which have same Product and Variant, and more than one part is placed on same Order So that I can manage the Ordering for such cases.

You are viewing reply posted by: abhi619 5 months ago.
Posted 5 months ago

Following query worked for me bcoz Order field is varchar:

SELECT Product, Variant, Part, [Order]
FROM tableA a1
WHERE EXISTS (SELECT 1 FROM tableA a2
              WHERE a2.Product = a1.Product AND
                    a2.Variant = a1.Variant AND
                    a2.[Order] = a1.[Order] AND
                    a2.Part <> a1.Part);