user1781272 user1781272 - 5 months ago 27
SQL Question

SQL Find depth of child row relative to top parent row

I have a a CTE that returns parent child relationships. If I select a where clause using PanelID i get all parents of the child panel. However, the depth is in reverse. I need the dept of the child panel to be 2 and the top parent to be 0.

WITH category_cte AS
(SELECT PanelID AS SourceID, PanelID, BP_DP, 0 AS depth FROM dbo.tblPanels
UNION ALL
SELECT CTE.SourceID, C.PanelID, C.BP_DP, CTE.depth + 1 AS depth
FROM dbo.tblPanels AS C INNER JOIN
category_cte AS CTE ON C.SCID = CTE.PanelID)
SELECT SourceID, PanelID, BP_DP, depth
FROM category_cte AS category_cte_1 where PanelID = x


Return

SourceID PanelID BP_DP depth
1240 1240 1 0
1446 1240 1 1
1434 1240 1 2

Answer

The obvious solution is to wrap your query in a subquery and use ROW_NUMBER to compute the depth in descending order:

WITH category_cte AS 
(SELECT PanelID AS SourceID, PanelID, BP_DP, 0 AS depth 
 FROM dbo.tblPanels

 UNION ALL

 SELECT CTE.SourceID, C.PanelID, C.BP_DP, CTE.depth + 1 AS depth
 FROM dbo.tblPanels AS C 
 INNER JOIN category_cte AS CTE ON C.SCID = CTE.PanelID)    
SELECT SourceID, PanelID, BP_DP, 
       ROW_NUMBER() OVER (ORDER BY depth DESC) -1 AS depth
FROM (
  SELECT SourceID, PanelID, BP_DP, depth
  FROM category_cte AS category_cte_1 
  where PanelID = x) AS t