Log in  ::  Register  ::  Not logged in

## need help and getting unique set

 Author Message dwain.c SSC-Forever Group: General Forum Members Points: 44489 Visits: 6431 I didn't realize that brute force was allowed.`CREATE TABLE #temp( set_id INT ,product CHAR(5) ,attribute INT)INSERT INTO #temp (set_id, product, attribute) VALUES (1,'A',10) ,(1,'A',11)INSERT INTO #temp (set_id, product, attribute) VALUES (2,'A',10),(2,'A',12)INSERT INTO #temp (set_id, product, attribute) VALUES (3,'A',10),(3,'A',11);INSERT INTO #temp (set_id, product, attribute) VALUES (4,'A',10),(4,'A',11);INSERT INTO #temp (set_id, product, attribute) VALUES (5,'A',10),(5,'A',12);INSERT INTO #temp (set_id, product, attribute) VALUES (6,'A',10),(6,'A',12),(6,'A',13);DECLARE @setid INT = 1, @maxid INT = (SELECT MAX(set_id) FROM #temp);-- Gotta start somewhereSELECT * INTO #temp2 FROM #temp WHERE set_id = @setid;WHILE @setid < @maxid BEGIN SELECT @setid = @setid + 1; INSERT INTO #temp2 SELECT * FROM #temp WHERE set_id = @setid AND EXISTS ( SELECT product, attribute FROM #temp WHERE set_id = @setid EXCEPT SELECT product, attribute FROM #temp2 GROUP BY set_id, product, attribute );ENDSELECT * FROM #temp2 ORDER BY set_id, product, attribute;GODROP TABLE #temp;DROP TABLE #temp2;` My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables