Re: Nested sort, trying again

From: vc <>
Date: 30 Sep 2005 13:43:55 -0700
Message-ID: <>

David Cressey wrote:
> "Bob Stearns" <> wrote in message
> news:KSX_e.17859$L45.15135_at_fe07.lga...
> > The question is: Is there an easy (relatively) way to produce this sort
> > without a recursive sql user defined function?
> Moving right along, there are two approaches you might want to take:
> First come up with a query that materializes the path (and at the same time
> the level) for you...
> So you can say something like
> select
> expr1 PATH,
> expr2 LEVEL,
> from ...
> where ...
> order by PATH;
> If PATH contains the entire materialized path in a string, with dots
> between the path elements, then ordering by path should be enough to put
> all your rows in the right order. LEVEL can be transformed to the right
> indentation, before printing the NAMEX.

Order by the m.p. will work only if the sibling were sorted in the desired order during the m.p. creation. That's rrarely the case.

The m.p. creation process for a hierarchy represented by a (parent, child) adjacency list is, in fact, a depth-first traversal.

> As to how you would materialize the path, I think there are examples of
> doing just that in Celko's book.
> The other approach involves updating LEFTX and RIGHTX whenever a user
> changes the NAMEX of an entry. That way you can keep it in alphabetical
> order. Now all you have to do at query time is order by LEFTX.
Received on Fri Sep 30 2005 - 22:43:55 CEST

Original text of this message