Controlling brothers order by recursive CTE?

Question

I have a CTE request that is looking for basic leaves and linings. but I have problems with choosing the order of choosing leaves between two brothers and sisters :

Each row in the table is declared as:

(childID INT ,parentID INT ,NAME NVARCHAR(30),location int)

Where location is the priority for IFF sorting, they are siblings.

So, I have this tree structure: these pairs have location priority:

For instance:

 `a` ( location=1) should be before `f` (location=2) `b` ( location=1) should be before `e` (location=2) `d` ( location=1) should be **before** `c` (location=2) 

The problem is that it seems to me that I must first order by childID in order to see the correct structure (sibling mismatch).

But, how should my order by look so that I can see the correct structure ( && sibling sorted )?

(in my example: d should appear before c )

Here is a working query that gives all the trees (unsorted brothers and sisters)

ps childID not indicating a sort. it's just a placeholder. as I said, the location between the two brothers is in the location column (here childId is sorted because the order in which I inserted the rows ...

Answer 1

You can calculate the path of the node tree in CTE and use it to sort

 ;WITH CTE AS( SELECT childID, parentID, 0 AS depth, NAME , location, cast(location as varbinary(max)) path FROM @myTable WHERE childID = parentID UNION ALL SELECT TBL.childID, TBL.parentID, CTE.depth + 1 , TBL.name ,TBL.location, cte.path + cast(TBL.location as binary(4)) FROM @myTable AS TBL INNER JOIN CTE ON TBL.parentID = CTE.childID WHERE TBL.childID<>TBL.parentID ) SELECT depth, childID, parentID, location, REPLICATE('----', depth) + name FROM CTE ORDER BY path 

Answer 2

The following is the i-th answer, modified in the Royi Namir query, to use left numbered number strings for the path:

 ;WITH CTE AS( SELECT childID, parentID, 0 AS depth, NAME , location, Cast( Right( '00000' + Cast( Location as VarChar(6) ), 6 ) as VarChar(1024) ) as Path FROM @myTable WHERE childID = parentID UNION ALL SELECT TBL.childID, TBL.parentID, CTE.depth + 1 , TBL.name ,TBL.location, Cast( cte.Path + '.' + Right( '00000' + Cast( Tbl.Location as VarChar(6) ), 6 ) as VarChar(1024) ) FROM @myTable AS TBL INNER JOIN CTE ON TBL.parentID = CTE.childID WHERE TBL.childID<>TBL.parentID ) SELECT depth, childID, parentID, location, REPLICATE('----', depth) + name FROM CTE ORDER BY path 

NB: not completed and written on vacation.

Separators ( . ) Are not required, but simplify the reading of the resulting values ​​and may simplify some operations, for example. finding common paths.