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.

Posted 5 months ago

Hi abhi619,

Refer below query.

SQL

CREATE TABLE #Test(Product VARCHAR(10),Variant VARCHAR(10),Part VARCHAR(10),[Order] INT)
INSERT INTO #Test VALUES('ab','vb','a1',1)
INSERT INTO #Test VALUES('ab','vb','a2',1)
INSERT INTO #Test VALUES('ab','vb','a3',2)
INSERT INTO #Test VALUES('ab','vb','a4',3)
INSERT INTO #Test VALUES('ab','vb','a5',4)

;WITH cte AS(
SELECT COUNT([Order]) Orders,Product,Variant,[Order] FROM #Test 
GROUP BY Product,Variant,[Order])

SELECT DISTINCT * FROM #Test 
WHERE [Order] IN (SELECT [Order] FROM cte WHERE Orders > 1)

DROP TABLE #Test

Output

Product   Variant   Part  Order

    ab          vb        a1     1

    ab          vb        a2     1


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);