SQLTeam.com | Weblogs | Forums

Reverse Recursive CTE to find Parent value


#1

I have sample Parent Child data like below.

declare @tab table (Item varchar(10),Parent varchar(10),Priority int)

insert into @tab
SELECT 'A',NULL,3,0
UNION ALL
SELECT 'A1','A',2,1
UNION ALL
SELECT 'A2','A',2,1
UNION ALL
SELECT 'A11','A1',2,2
UNION ALL
SELECT 'A12','A1',1,2
UNION ALL
SELECT 'A21','A2',3,2
UNION ALL
SELECT 'A22','A2',2,2

select * from @tab

now i need to traverse (Leaf node to subsequent Parent node) in reverse to set its Subsequent parent node Priority based on its all children minimum priority.

for example, for Children A11 and A12 minimum priority is 1 and that 1 should be set for A1.
for Children A21 and A22 minimum priority is 2 and that 2 should be set for A2.
now A1 priority is 1
A2 priority is 2

similarly for Children A1 and A2 minimum priority is 1 and that 1 should be set for A.

so expected output as below.

SELECT 'A',NULL,1,0
UNION ALL
SELECT 'A1','A',1,1
UNION ALL
SELECT 'A2','A',2,1
UNION ALL
SELECT 'A11','A1',2,2
UNION ALL
SELECT 'A12','A1',1,2
UNION ALL
SELECT 'A21','A2',3,2
UNION ALL
SELECT 'A22','A2',2,2

Please guide me how to do it.

Thanks in advance


#2

Your table definition and inserts don't agree. Can we start with some code that works?


#3

sorry pls find below updated query.

declare @tab table (Item varchar(10),Parent varchar(10),Priority int,[Level] int)

insert into @tab
SELECT 'A',NULL,3,0
UNION ALL
SELECT 'A1','A',2,1
UNION ALL
SELECT 'A2','A',2,1
UNION ALL
SELECT 'A11','A1',2,2
UNION ALL
SELECT 'A12','A1',1,2
UNION ALL
SELECT 'A21','A2',3,2
UNION ALL
SELECT 'A22','A2',2,2

select * from @tab


#4

Using a recursive CTE, something like this:

;WITH cte AS
(
	SELECT 
		Item,
		Parent,
		Priority,
		MIN(priority) OVER (PARTITION BY Parent) AS MinPriority,
		Level
	FROM
		@tab t1
	WHERE
		NOT EXISTS (SELECT * FROM @tab t2 WHERE t2.parent = t1.Item)
		
	UNION ALL
	
	SELECT
		t1.Item,
		t1.Parent,
		c.MinPriority,
		MIN(c.MinPriority) OVER (PARTITION BY t1.Parent),
		t1.Level
	FROM
		cte c
		INNER JOIN @tab t1 ON
			t1.Item = c.Parent
)
SELECT Item,Parent,MIN(cte.Priority), Level AS NewPriority 
FROM cte GROUP BY Item, parent, Level;

What are the 3rd and 4th columns in your desired output? I am guessing they are the new priority and the level.


#5

Thank you JamesK...Perfect answer.
very much appreciated.


#6

There may be a way to do this where you're not recalculating the path every time. How many nodes are in your hierarchy and how often is the hierarchy updated?