Recursive rows select?

Question

Hello,
I am not sure if it should be called recursive but I did not come up with better Question title.

I have a table of orders.

OrderID, PreviousOrderID, OrderName, OrderDate...

Column PreviousOrderID contains the OrderID of the previous order with same products as in current OrderID. (Products are in 2nd table OrderDetails, it is not important at this moment.)

Table example.

10 | 07 | Vegetable | 18.8.2021 //OrderID 10 has PreviousOrderID = 07, so I must find OrderID = 07
09 | 03 | Meat | 16.8.2021
08 | null | Meat | 14.8.2021
07 | 05 | Vegetable | 12.8.2021 //Order ID 07 has PreviousOrderID = 05, so I must find OrderID = 05
06 | null | Nuts | 10.8.2021
05 | 03 | Vegetable | 08.8.2021 //Order ID 05 has PreviousOrderID = 03, so I must find OrderID = 03
04 | 03 | Vegetable | 06.8.2021
03 | null | Vegetable | 04.8.2021 //Order ID 03 has PreviousOrderID = null, so I do not do anything

I need to list order Nr. 10 plus all connected Previous orders, result should be like this:
10 | 07 | Vegetable | 18.8.2021
07 | 05 | Vegetable | 12.8.2021
05 | 03 | Vegetable | 08.8.2021
03 | null | Vegetable | 04.8.2021

I could do it quite easily in c# but is there an way how to create SELECT for it? Sorting when searching could be important because if PreviousOrder = null, it should be the last connected order.

Thank you in advance for help.

Petr

Answer 1

Hi @petr p ,

Welcome to Microsoft Q&A!

You could use the Recursive CTE and please refer below query and check whether it is working.

create table #temp   
(  
OrderID char(2),  
PreviousOrderID char(2),  
OrderName varchar(20),  
OrderDate date  
)  
  
insert into #temp values  
('10','07','Vegetable', '2021-08-18'),   
('09','03','Meat','2021-08-16'),   
('08',null,'Meat','2021-08-14'),   
('07','05','Vegetable','2021-08-12'),   
('06',null,'Nuts','2021-08-10'),   
('05','03','Vegetable','2021-08-08'),   
('04','03','Vegetable','2021-08-06'),   
('03',null,'Vegetable','2021-08-04')   
  
;WITH cte AS (  
    SELECT OrderID, PreviousOrderID, OrderName, OrderDate          
    FROM #temp  
    WHERE OrderID =10  
    UNION ALL  
    SELECT a.OrderID, a.PreviousOrderID,a.OrderName,a.OrderDate  
    FROM #temp a  
    INNER JOIN cte b   
    ON b.PreviousOrderID = a.OrderID  
)  
SELECT * FROM cte;  

Output:

OrderID	PreviousOrderID	OrderName	OrderDate  
10	07	Vegetable	2021-08-18  
07	05	Vegetable	2021-08-12  
05	03	Vegetable	2021-08-08  
03	NULL	Vegetable	2021-08-04  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Melissa thank you! I appreciate it. This is more clear to me :-)

Answer 3

Hi @petr p ，

Based on your updated requirement, you could refer below:

;WITH cte AS (  
    SELECT OrderID, PreviousOrderID, OrderName, OrderDate          
    FROM #temp  
    WHERE OrderID =10  
    UNION ALL  
    SELECT a.OrderID, a.PreviousOrderID,a.OrderName,a.OrderDate  
    FROM #temp a  
    INNER JOIN cte b   
    ON b.PreviousOrderID = a.OrderID  
)  
SELECT * FROM cte  
UNION  
SELECT a.* FROM #temp a   
inner join cte b on a.PreviousOrderID=b.PreviousOrderID  
order by OrderID desc  

Output:

OrderID	PreviousOrderID	OrderName	OrderDate  
10	07	Vegetable	2021-08-18  
07	05	Vegetable	2021-08-12  
05	03	Vegetable	2021-08-08  
04	03	Vegetable	2021-08-06  
03	02	Vegetable	2021-08-06  
02	NULL	Vegetable	2021-08-03  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hello. I just realized that I also need records that are not in "direct line" because users sometimes does not reference last previous order but some older order. I edited input data little bit, see below.

It means I also need all records where OrderID = any PreviousOrderID that is in output of select in your first answer. I really hope you understand me...

And as I said these kind of selects are really hard for me, I have no idea how to tune up your select to do what I need.

 insert into #temp values
 ('10','07','Vegetable', '2021-08-18'), 
 ('09','08','Meat','2021-08-16'), 
 ('08',null,'Meat','2021-08-14'), 
 ('07','05','Vegetable','2021-08-12'), 
 ('06',null,'Nuts','2021-08-10'), 
 ('05','03','Vegetable','2021-08-08'), 
 ('04','03','Vegetable','2021-08-06'), 
 ('03','02','Vegetable','2021-08-06'),
 ('02',null,'Vegetable','2021-08-03'),
 ('01',null,'Vegetable','2021-08-02')

Output should include also row where OrderID=04:

         OrderID    PreviousOrderID    OrderName    OrderDate
         10    07    Vegetable    2021-08-18
         07    05    Vegetable    2021-08-12
         05    03    Vegetable    2021-08-08
         04    03    Vegetable    2021-08-06
         03    02    Vegetable    2021-08-06
         02    NULL    Vegetable    2021-08-03

Answer 5

Hello Melissa,
thank you!

Select is simple but for me hard to understand, I never got used to these kinds of selects. I found quite good topic on that in Microsoft Learn here https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15#guidelines-for-defining-and-using-recursive-common-table-expressions

Petr