Finding Identical orders

Question

question

Alex Uren asked Apr 18, '10

Finding Identical orders

Hi there - I have a table that has details of customer orders. I'm trying to find orders that are the same so that I can pick them quicker - ie out of 3,000 orders, there may be 5 orders where all customers have ordered apples, oranges and peaches (quantity is not important, just the product ID)

table includes fields - orderID, qtyOrd, ProductID, customerID

want to return preferabley something that has a new ID called orderSet which is a number that groups all similar orders

any help very appreciated!!

delete-duplicates

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-04-18T19:38:27Z

Scot Hauder answered Apr 18, '10

;WITH CTE1 AS (            
    SELECT OrderID,(SELECT CAST(ProductID AS VARCHAR(20)) + ' '            
                    FROM OrderDetail            
                    WHERE OrderID = o1.OrderID            
                    ORDER BY ProductID            
                    FOR XML PATH('')) [Products]            
    FROM OrderDetail o1            
)            
,CTE2 AS (            
    SELECT OrderID, Products             
    FROM CTE1            
    GROUP BY OrderID, Products            
)            
SELECT DENSE_RANK() OVER (ORDER BY PRODUCTS) [OrderSet]            
,OrderID            
,Products            
FROM CTE2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question