Recursion? How can I group orders by common items

Question

The setup is this. I have Orders and OrderDetails and the OrderDetails have an Item Number. Orders have many OrderDetails. Out of ~1000 Orders with ~10,000 OrderDetail lines, I need the Top 16 Orders that have the most Items in common.

After a week of research, here's my attempt. When I got to the 8th iteration, I had to stop. It was a loop but I couldn't figure out how to set the temp table names dynamically. I also couldn't figure out how to determine when I had the 16 best Orders.


IF OBJECT_ID('tempdb..#PartNums') IS NOT NULL DROP Table #PartNums
--Gets the part number that is in the most orders
CREATE TABLE #PartNums (ctr int Identity, PartNum varchar(50), CONT int)
INSERT INTO #PartNums SELECT TOP(1) D.PartNum, Count(D.PartNum) AS CONT FROM OrderDetails D
group by  D.PartNum
order by CONT desc

--Gets the orders that have the number one part number in it
IF OBJECT_ID('tempdb..#Orders') IS NOT NULL DROP Table #Orders
CREATE TABLE #Orders ( Id int, Ord1 varchar(50), Ord2 varchar(50),PartNum varchar(50))
INSERT INTO #Orders SELECT O.Id,O.Ord1,O.Ord2, D.PartNum
FROM            OrderDetails D INNER JOIN
                         Orders O ON D.OrderId = O.Id
                         where D.PartNum IN (SELECT partNum from #PartNums WHERE ctr = 1)

-- Using just the orders that have the number 1 part number
-- Get the part number that is next most popular in the orders
--Exclude the first part number from the grouping

INSERT INTO #PartNums SELECT TOP(1)  D.PartNum, Count(D.PartNum) as CONT from OrderDetails D inner join #Orders O ON O.Id = D.OrderId
where NOT D.PartNum IN (SELECT partNum from #PartNums)
group by D.PartNum
order by CONT desc


--Gets the orders that have the number one part number in it
IF OBJECT_ID('tempdb..#Orders2') IS NOT NULL DROP Table #Orders2
CREATE TABLE #Orders2 ( Id int, Ord1 varchar(50), Ord2 varchar(50),PartNum varchar(50))
INSERT INTO #Orders2 SELECT O.Id,O.Ord1,O.Ord2, D.PartNum
FROM            OrderDetails D INNER JOIN
                         #Orders O ON D.OrderId = O.Id
                         where D.PartNum IN (SELECT partNum from #PartNums WHERE ctr = 2)


INSERT INTO #PartNums SELECT TOP(1)  D.PartNum, Count(D.PartNum) as CONT from OrderDetails D inner join #Orders2 O ON O.Id = D.OrderId
where NOT D.PartNum IN (SELECT partNum from #PartNums)
group by D.PartNum
order by CONT desc


--Gets the orders that have the number one part number in it
IF OBJECT_ID('tempdb..#Orders3') IS NOT NULL DROP Table #Orders3
CREATE TABLE #Orders3 ( Id int, Ord1 varchar(50), Ord2 varchar(50),PartNum varchar(50))
INSERT INTO #Orders3 SELECT O.Id,O.Ord1,O.Ord2, D.PartNum
FROM            OrderDetails D INNER JOIN
                         #Orders2 O ON D.OrderId = O.Id
                         where D.PartNum IN (SELECT partNum from #PartNums WHERE ctr = 3)


INSERT INTO #PartNums SELECT TOP(1)  D.PartNum, Count(D.PartNum) as CONT from OrderDetails D inner join #Orders3 O ON O.Id = D.OrderId
where NOT D.PartNum IN (SELECT partNum from #PartNums)
group by D.PartNum
order by CONT desc


--Gets the orders that have the number one part number in it
IF OBJECT_ID('tempdb..#Orders4') IS NOT NULL DROP Table #Orders4
CREATE TABLE #Orders4 ( Id int, Ord1 varchar(50), Ord2 varchar(50),PartNum varchar(50))
INSERT INTO #Orders4 SELECT O.Id,O.Ord1,O.Ord2, D.PartNum
FROM            OrderDetails D INNER JOIN
                         #Orders3 O ON D.OrderId = O.Id
                         where D.PartNum IN (SELECT partNum from #PartNums WHERE ctr = 4)


INSERT INTO #PartNums SELECT TOP(1)  D.PartNum, Count(D.PartNum) as CONT from OrderDetails D inner join #Orders4 O ON O.Id = D.OrderId
where NOT D.PartNum IN (SELECT partNum from #PartNums)
group by D.PartNum
order by CONT desc


--Gets the orders that have the number one part number in it
IF OBJECT_ID('tempdb..#Orders5') IS NOT NULL DROP Table #Orders5
CREATE TABLE #Orders5 ( Id int, Ord1 varchar(50), Ord2 varchar(50),PartNum varchar(50))
INSERT INTO #Orders5 SELECT O.Id,O.Ord1,O.Ord2, D.PartNum
FROM            OrderDetails D INNER JOIN
                         #Orders4 O ON D.OrderId = O.Id
                         where D.PartNum IN (SELECT partNum from #PartNums WHERE ctr = 5)

INSERT INTO #PartNums SELECT TOP(1)  D.PartNum, Count(D.PartNum) as CONT from OrderDetails D inner join #Orders5 O ON O.Id = D.OrderId
where NOT D.PartNum IN (SELECT partNum from #PartNums)
group by D.PartNum
order by CONT desc


--Gets the orders that have the number one part number in it
IF OBJECT_ID('tempdb..#Orders6') IS NOT NULL DROP Table #Orders6
CREATE TABLE #Orders6 ( Id int, Ord1 varchar(50), Ord2 varchar(50),PartNum varchar(50))
INSERT INTO #Orders6 SELECT O.Id,O.Ord1,O.Ord2, D.PartNum
FROM            OrderDetails D INNER JOIN
                         #Orders5 O ON D.OrderId = O.Id
                         where D.PartNum IN (SELECT partNum from #PartNums WHERE ctr = 6)

INSERT INTO #PartNums SELECT TOP(1)  D.PartNum, Count(D.PartNum) as CONT from OrderDetails D inner join #Orders6 O ON O.Id = D.OrderId
where NOT D.PartNum IN (SELECT partNum from #PartNums)
group by D.PartNum
order by CONT desc

--Gets the orders that have the number one part number in it
IF OBJECT_ID('tempdb..#Orders7') IS NOT NULL DROP Table #Orders7
CREATE TABLE #Orders7( Id int, Ord1 varchar(50), Ord2 varchar(50),PartNum varchar(50))
INSERT INTO #Orders7 SELECT O.Id,O.Ord1,O.Ord2, D.PartNum
FROM            OrderDetails D INNER JOIN
                         #Orders6 O ON D.OrderId = O.Id
                         where D.PartNum IN (SELECT partNum from #PartNums WHERE ctr = 7)


INSERT INTO #PartNums SELECT TOP(1)  D.PartNum, Count(D.PartNum) as CONT from OrderDetails D inner join #Orders7 O ON O.Id = D.OrderId
where NOT D.PartNum IN (SELECT partNum from #PartNums)
group by D.PartNum
order by CONT desc


--Gets the orders that have the number one part number in it
IF OBJECT_ID('tempdb..#Orders8') IS NOT NULL DROP Table #Orders8
CREATE TABLE #Orders8( Id int, Ord1 varchar(50), Ord2 varchar(50),PartNum varchar(50))
INSERT INTO #Orders8 SELECT O.Id,O.Ord1,O.Ord2, D.PartNum
FROM            OrderDetails D INNER JOIN
                         #Orders7 O ON D.OrderId = O.Id
                         where D.PartNum IN (SELECT partNum from #PartNums WHERE ctr = 7)

SELECT DISTINCT OrderId FROM OrderDetails where PartNum in (SELECT partNum from #PartNums);

SELECT * FROM #Orders8;

Welcome to Stack Overflow. To help us help you, please take the [Tour](https://stackoverflow.com/tour) and read through [How To Ask](https://stackoverflow.com/help/how-to-ask). Take a look at this [well-structured question](https://stackoverflow.com/questions/60078622/merge-two-tables-in-one-sql-query-and-make-the-date-values-unique), then [edit](https://stackoverflow.com/posts/60344350/edit) your question with the details needed to create [a Minimal, Complete, and Verifiable Example for database-related questions](https://dba.stackexchange.com/help/minimal-reproducible-example). — Eric Brandt, Feb 21 '20 at 18:38
Not really, I'm afraid. Take a look at [Writing the Perfect Question](https://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/) by [Jon Skeet](https://stackoverflow.com/users/22656/jon-skeet) and [How to post a T-SQL question on a public forum](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for a clearer idea of what the volunteers on the site are looking for. — Eric Brandt, Feb 21 '20 at 19:48
Why did you add the same code over and over to your question? That's just noise and almost the opposite of the [mcve] that everybody likes to see here. — Robert, Feb 21 '20 at 23:53

Luuk · Answer 1 · 2020-02-24T09:34:53.637

Because no DDL was given, i create a 'simple' example on this FIDDLE

SELECT 
    Parts, 
    STRING_AGG(id,',') WITHIN GROUP (ORDER BY Id) as PartsCombinationInOrder,
    COUNT(*)           as NumberOfTimes
FROM (
  SELECT
     Orders.Id, STRING_AGG(Order_details.Part_no,',') WITHIN GROUP (ORDER BY Order_details.Part_no) as Parts
  FROM Orders
  INNER JOIN Order_Details on Order_Details.Order_id = Orders.Id
  GROUP BY Orders.Id
) x
GROUP BY x.Parts
ORDER BY COUNT(*) DESC;

For a short explanation on ho STRING_AGG() works see: STRING_AGG

This will first get all articles on every order, than group on the combination of these articles, and order them descending. The most favorite combination should be on top.

EDIT:

Another attempt to find all combinations of articles in orders

WITH Orders as(
    SELECT * FROM (VALUES(1),(2),(3),(4)) Orders(Order_Id)
    )
    , Order_Details as (
    SELECT * FROM (VALUES
            (1,1,1),(1,2,2),(1,3,3),(1,4,4),(1,5,5),(1,6,6),
            (2,1,1),(2,2,2),(2,3,3),(2,4,4),(2,5,5),(2,6,6),
            (3,1,1),(3,2,2),(3,3,3),(3,4,4),(3,5,5),(3,6,6), 
            (4,1,1),(4,2,2),(4,3,7),(4,4,3),(4,5,5),(4,6,6)
        ) Order_Details(Order_id,Line_no,Part_no) 
    ),combinations as (
    SELECT o.Order_Id, 
        od1.Line_no as Line1, od1.Part_no as Part1, 
        od2.Line_no as Line2, od2.Part_no as Part2, 
        od3.Line_no as Line3, od3.Part_no as Part3
    FROM Orders o
    INNER JOIN Order_Details od1 ON od1.Order_id = o.Order_Id       
    INNER JOIN Order_Details od2 ON od2.Order_id = o.Order_Id AND od2.Line_no > od1.Line_no
    INNER JOIN Order_Details od3 ON od3.Order_id = o.Order_Id AND od3.Line_no > od2.Line_no AND od3.Line_no > od1.Line_no
    )
SELECT c.Order_Id, c.Part1, c.Part2, c.Part3, q.count
FROM combinations c
CROSS APPLY (SELECT count(*) as count FROM combinations q WHERE q.Part1=c.Part1 and q.Part2=c.Part2 and q.Part3=c.Part3) q
ORDER BY q.count DESC,c.Part1,c.Part2,c.Part3, c.Order_Id;

output:

Order_Id    Part1       Part2       Part3       count
----------- ----------- ----------- ----------- -----------
1           1           2           3           4
2           1           2           3           4
3           1           2           3           4
4           1           2           3           4
1           1           2           5           4
2           1           2           5           4
✂✂✂✂✂
4           3           5           6           4
1           1           2           4           3
2           1           2           4           3
3           1           2           4           3
1           1           3           4           3
2           1           3           4           3
3           1           3           4           3
1           1           4           5           3
✂✂✂✂✂
2           4           5           6           3
3           4           5           6           3
4           1           2           7           1
4           1           7           3           1
4           1           7           5           1
4           1           7           6           1
4           2           7           3           1
4           2           7           5           1
4           2           7           6           1
4           7           3           5           1
4           7           3           6           1
4           7           5           6           1

(80 rows affected)

I Think this piece is easy expandable to have 'Part4','Part5',etc, which allows combinations of more than 3 articles which is show above.

The example returns a list of orders that are "exactly" the same. The Orders are not the same but have many parts in common. I need to know which Orders have the most common part numbers down to the least. I have worked on the [FIDDLE](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=0faf50818d8709c5373d597350da10ae) and added comments to it to describe what I was trying to achieve. — James, Feb 22 '20 at 20:08
OK, i understand, will tink about this..... , the link to the fiddle you posted is the same as the link i posted... (your changes are lost?) — Luuk, Feb 22 '20 at 20:23
Here is the updated [Fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8df334a2dad7a691e9897c0e91e95f22) — James, Feb 23 '20 at 14:15
Note: Step2 returns the same records as Step1 (expect for article3), also note that in step 2 you should not only leave out article3, but the orders selected there should also have a line with article3 otherwise there will no be a combination of article3 with the found article.... (difficult to explain this is....) — Luuk, Feb 23 '20 at 15:09
Yes, everything you said it true, especially (difficult to explain). I'm sure the problem is how I'm trying to explain the question by using MY attempt at a solution that I'm now positive is wrong. When you're helping to find a solution, you should not use any of my previous attempts. So, with that in mind, I want to reframe the question to make it as clear as I can in the next comment. — James, Feb 23 '20 at 23:01
`combinations` are selected on Line_no of order_details. This could be a problem if one order has Part_no `1,2,3` and another `1,3,2`. This example *assumes* Part_no are ordered the same way as Line_no (which, of course, the are not in real life) — Luuk, Feb 24 '20 at 09:41

Recursion? How can I group orders by common items

1 Answers1