tsql - A real recursion with CTE?

Question

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 :

1. team1
2. team2
3. team3
4. team1-1
5. team3-1
6. team1-2

but

1. team1
2. team1-1
3. team1-2
4. team2
5. team3
6. team3-1

Thanks

Answer 1

You can build a column to sort by when you do the recursion.

Something like this:

declare @t table
(
  ID int,
  ParentID int,
  Name varchar(10)
);

insert into @T values
(1, null, 'team1'),
(2, null, 'team2'),
(3, null, 'team3'),
(4, 1, 'team1-1'),
(5, 1, 'team1-2'),
(6, 3, 'team3-1');

with C as
(
  select T.ID,
         T.ParentID,
         T.Name,
         cast(right(100000 + row_number() over(order by T.ID), 5) as varchar(max)) as Sort
  from @T as T
  where T.ParentID is null
  union all
  select T.ID,
         T.ParentID,
         T.Name,
         C.Sort+right(100000 + row_number() over(order by T.ID), 5)
  from @T as T
    inner join C
      on T.ParentID = C.ID
)
select *
from C
order by Sort

Result:

ID          ParentID    Name       Sort
----------- ----------- ---------- ------------
1           NULL        team1      00001
4           1           team1-1    0000100001
5           1           team1-2    0000100002
2           NULL        team2      00002
3           NULL        team3      00003
6           3           team3-1    0000300001