CODEWITHSUNDEEP
sql-servert-sqlcommon-table-expression
Fabio M.
Fabio M.

Reputation: 296

T-SQL recursive query that sorts by parent level only

I am trying to achieve the following requirement:

I have a hierarchy of products with their spare parts. Those spare parts can have other parts in a graph that can potentially have several layers of depth.

My goal is to write a T-SQL query that allows me to order by any column of the table (in the example Product, Price or EndOfWarrantyDate) but keeping the hierarchy. In other words, I want to order only the parent items, without worrying about the children that should come into play ONLY to follow their parents.

In the example below, if you notice, the green rows are sorted by "EndOfWarrantyDate", but I still want to show the children and sub-children next to their parent (I don't care about ordering the children, as long as the hierarchy is respected).

Table Example

NOTE: the Id in the table above is just there to better identify the hierarchy, in the db I have a normal identity column as Id.

And, if possible, I would like to do that in a single SQL query, without having to collect the parent items first, order them and then get the related children and subchildren.

My CTE at the moment looks like this, but I have no idea how to achieve the sorting. Tried with a PARTITION, but don't know how to apply it properly to solve my issue.

WITH
  cteProducts (Id, Product, ParentProductId, Price, EndOfWarrantyDate, Depth, RootId)
  AS
  (
-->>>>>>>>>>Root>>>>>>>>>>>>>>>>>
    SELECT Id, Product, ParentProductId, Price, EndOfWarrantyDate, 0 as Depth, Id as RootId
    FROM Products
    WHERE ParentProductId IS NULL
-->>>>>>>>>>Root>>>>>>>>>>>>>>>>>
    UNION ALL
-->>>>>>>>>>Children>>>>>>>>>>>>>>>>>    
    SELECT e.Id, e.Product, e.ParentProductId, e.Price, e.EndOfWarrantyDate, r.Depth + 1, r.RootId
    FROM Products e
      INNER JOIN cteProducts r
        ON e.ParentProductId = r.Id
-->>>>>>>>>>Children>>>>>>>>>>>>>>>>>
  )
SELECT
  Id, Product, ParentProductId, Price, EndOfWarrantyDate, Depth, RootId
FROM cteProducts
--ORDER BY RootId, EndOfWarrantyDate

Any help is very appreciated, thank you!

Upvotes: 1

Views: 229

Answers (1)

Sander
Sander

Reputation: 4042

Preserve the parent EndOfWarrantyDate as part of your recursive CTE. This way you can sort on it in the end.

Sample data

declare @Products table
(
    Id nvarchar(10),
    Product nvarchar(20),
    ParentProductId nvarchar(10),
    Price int,
    EndOfWarrantyDate date
);

insert into @Products (Id, Product, ParentProductId, Price, EndOfWarrantyDate) values
('1',       'Vacuum',           null,   130,    '2013-04-02'),
('1.1',     'Power Unit',       '1',    200,    '2024-01-01'),
('1.1.1',   'Unit part',        '1.1',  100,    '2024-01-01'),
('1.2',     'Filter',           '1',    10,     '2022-07-15'),
('2',       'Laptop',           null,   600,    '2023-06-01'),
('2.1',     'Hard Disk',        '2',    200,    '2024-03-01'),
('3',       'Washing Machine',  null,   1000,   '2023-12-01');

Solution

WITH
  cteProducts (Id, Product, ParentProductId, Price, EndOfWarrantyDate, Depth, RootId, ParentDate)
  AS
  (
-->>>>>>>>>>Root>>>>>>>>>>>>>>>>>
    SELECT Id, Product, ParentProductId, Price, EndOfWarrantyDate, 0 as 'Depth', Id as 'RootId'
            ,EndOfWarrantyDate as 'ParentDate' -- new field
    FROM @Products
    WHERE ParentProductId IS NULL
-->>>>>>>>>>Root>>>>>>>>>>>>>>>>>
    UNION ALL
-->>>>>>>>>>Children>>>>>>>>>>>>>>>>>    
    SELECT e.Id, e.Product, e.ParentProductId, e.Price, e.EndOfWarrantyDate, r.Depth + 1, r.RootId
            ,r.ParentDate -- new field
    FROM @Products e
      INNER JOIN cteProducts r
        ON e.ParentProductId = r.Id
-->>>>>>>>>>Children>>>>>>>>>>>>>>>>>
  )
SELECT ParentDate,
  Id, Product, ParentProductId, Price, EndOfWarrantyDate, Depth, RootId
FROM cteProducts
order by ParentDate, Id; -- new field as first sort field

Result

ParentDate Id         Product              ParentProductId Price       EndOfWarrantyDate Depth       RootId
---------- ---------- -------------------- --------------- ----------- ----------------- ----------- ----------
2013-04-02 1          Vacuum               NULL            130         2013-04-02        0           1
2013-04-02 1.1        Power Unit           1               200         2024-01-01        1           1
2013-04-02 1.1.1      Unit part            1.1             100         2024-01-01        2           1
2013-04-02 1.2        Filter               1               10          2022-07-15        1           1
2023-06-01 2          Laptop               NULL            600         2023-06-01        0           2
2023-06-01 2.1        Hard Disk            2               200         2024-03-01        1           2
2023-12-01 3          Washing Machine      NULL            1000        2023-12-01        0           3

Upvotes: 1

Related Questions