Jeff Moden (5/24/2012)

---

dwain.c (5/24/2012)

---

Jeff Moden (10/24/2010)

---

For the simple task of sorting in the correct order, we'll use the "Hierarchical Path" method. To build it, we'll use a "Recursive CTE". Look that up in Books Online for a better understanding of them. I will tell you right now that they are NOT a panacea. Hierarchies are one of the few places where I don't mind the use of recursive CTE's because they're normally RBAR code. In the example that follows, the recursive CTE is "layered" or (as Celko calls it), "lasagna" code. It processes a full set (one full level) of information for each iteration which makes it non-RBAR code. It's still a bit slower than a more lengthy method but it takes a huge hierarchy to really appreciate the difference so the recursive CTE is usually good enough.

Oh my! No fair! I love posting recursive CTE solutions and you beat me to it.

Only by a year and eight months. 🙂 (Just having a little fun here).

Shifting gears, Dwaine, all is not lost here. If you could figure out a way to make this kind of "lasagne" recursive CTE faster, I know a lot of people (especially me) that would greatly appreciate it. The only other way that I know to build the Hierarchical path is with a slightly faster While Loop and even though it's not a RBAR While Loop, it still doesn't sit so well with me that I have to use one here.