Re: Nested sort, trying again

From: David Cressey <>
Date: Fri, 30 Sep 2005 19:36:00 GMT
Message-ID: <Asg%e.7304$>

"Bob Stearns" <> wrote in message news:KSX_e.17859$L45.15135_at_fe07.lga...

> I thought everyone on here would be familiar with Joe Celko's Nested Set
> representation of a tree; he has an entire book devoted to it: _Trees
> and Hierarchies in SQL for Smarties_; ISBN 1-55860-920-2. It uses the
> left and right indices to represent containership. For instance the tree
> a(b(c,d),e(f(g,h))) (sorry for the linear representation, ASCII does not
> lend itself to drawing trees) would be represented by the rows:

Nested Sets have been discussed quite a number of times in this newsgroup. I think most of us, myself included, need more than a couple of columns named LEFTX and RIGHTX to know that we are dealing with an instance of Nested Sets. I note that Joe Celko himself didn't respond to this inquiry, even though he's a frequent contributor.

> 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


    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.

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 - 21:36:00 CEST

Original text of this message