Recursive CTE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Recursive CTE

Author

Topic

whitegrs
Starting Member

5 Posts

Posted - 2011-06-08 : 15:12:20

Hello all

I have a self referencing table and I need to to recursively find the boss of the boss of the boss, so to speak - and when I find that row, I need to select a value from it. Can anyone give me a simple example of walking 'up' the tree? Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-08 : 15:51:35

google is your friend

http://msdn.microsoft.com/en-us/library/ms186243.aspx

If you don't have the passion to help people, you have no passion

whitegrs
Starting Member

5 Posts

Posted - 2011-06-08 : 16:01:51

quote:
Originally posted by yosiasz

google is your friend

http://msdn.microsoft.com/en-us/library/ms186243.aspx

If you don't have the passion to help people, you have no passion

I checked that example already - If you read my question carefully you will see the difference.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-08 : 16:45:10

yes sir. ok I see what you mean now. would this help?

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1e162296-ae31-46f4-8e0a-a71dc6892941

If you don't have the passion to help people, you have no passion

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-06-09 : 15:33:49

walking UP the tree is very similar to walking DOWN the tree. It is just a matter of tweaking JOIN correlation with id and parentid.
and just returning the ultimate parent means just returning the top 1 row in desc order. If this doesn't work for you then post executable DDL/DML and your expected results.


declare @h table (id int, parentid int NULL)
insert @h 
select 10, 5 union all
select 9, 5 union all
select 8, 5 union all
select 7, 5 union all
select 6, 5 union all
select 5, 3 union all
select 4, 3 union all
select 3, 1 union all
select 2, 1 union all
select 1, null

declare @start int
set @start = 10

;with cte (id, parentid, lev)
as 
(
       select id, parentid, 1
       from   @h
       where  id = @start
       union all
       select h.id, h.parentid, c.lev + 1
       from   cte c
       join   @h h on h.id = c.parentid
)
select top 1 id [ultimate parent]
from cte 
order by lev desc

OUTPUT:
ultimate parent
---------------
1

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources