I just discovered CTE this afternoon and after enjoying them during 2 hours, I realized that they did not perform a common recursion like we all learned in any other language.
What I mean is, I always see recursion like a tree search. So i was expecting CTE to go all the way down to the first leaf it founds, but no. He works by layers. It begins by the head, then all the branches, then all the sub-branches, etc... and THEN the leaves.
Is there a way to make it search differently ? Perhaps did I miss something... I work on SQL Server 2005 (non, I can't change for the 2008)
To make things clear, I don't want :
- team1
- team2
- team3
- team1-1
- team3-1
- team1-2
but
- team1
- team1-1
- team1-2
- team2
- team3
- team3-1
Thanks
See Question&Answers more detail:os