Re: Nested sort, trying again

"Bob Stearns" <rstearns1241_at_charter.net> 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

select

    expr1 PATH,
    expr2 LEVEL,
    NAMEX
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