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