Re: Nested sort, trying again

"Bob Stearns" <rstearns1241_at_charter.net> wrote in message news:UOT_e.7500$wg7.6034_at_fe06.lga...
> I have a table (see below for ddl) which implements a set of locations
> for an organization, from the whole organization to shelves in a
> particular room. Users never see the primary key, only the NAMEX,
> usually as a drop down selection list, sometimes a single selection
> other times a multiselection. Users, being users, want the ability to
> change names and to see the names in sorted order at each level. If I
> didn't have to have changeable names, I could define the data in
> alphabetic order. Given that I can't do that, is there (relatively) easy
> way to present the data in a sorted manner? This is the same as seeing
> you directory tree in Windows explorer with the name chosen as the sort
> key. The root directory is sorted in filename order, then every
> directory under in it also in name order, down an arbitrary number of
> levels. In my examples, users attach people, functions, things etc. to
> locations at any level, can add additional locations at any level
> (dividing up a room, adding shelving, buying a new building, etc.).
> Because history is important, locations can only become inactive, never
> deleted. Because of the arbitrary number of levels, I can not join any
> other table to achieve the sort I want.
>
> CREATE TABLE IS3.LOCATIONS (
> LOC_ID INTEGER GENERATED BY DEFAULT AS IDENTITY
> (START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL,
> NAMEX VARCHAR(30) NOT NULL,
> LEFTX INTEGER NOT NULL,
> RIGHTX INTEGER NOT NULL,
> ENTITY_ID INTEGER NOT NULL,
> INACTIVE CHARACTER(25),
> PRIMARY KEY(LOC_ID)
> )
> GO
> ALTER TABLE IS3.LOCATIONS
> ADD CONSTRAINT INACTIVE
> CHECK (INACTIVE in ('Y','N'))
> GO
> ALTER TABLE IS3.LOCATIONS
> ADD CONSTRAINT SQL040717191150980
> FOREIGN KEY(ENTITY_ID)
> REFERENCES IS3.ENTITIES_PUB(ENTITY_ID)
> ON DELETE NO ACTION
> ON UPDATE NO ACTION
> GO

Bob,

What I'm not clear on is whether you have gotten the answer to your original question.

I offered two alternatives, the second of which has not been commented on.

Let me reiterate, although I'm having trouble making my proposal as clear as I would like:

There is more than one way to maintain LEFTX and RIGHTX in the above table, that will produce trees that are logically equivalent (in terms of parentage) but
present the siblings in a different order.

So if I have the tree

a(b), c (d,e), f (g,h)

represented in LEFTX, RIGHTX, and if I now rename c to be x, what I want is

a(b), f(g,h), x (d, e).

I've maintained the logical structure of the tree, but I've put c (now known as x) where it "belongs" alphabetically. This can be done by suitable manipulation of LEFTX and RIGHTX, but I'm too lazy to figure out how.

Now, retrieving the nodes in the order you want is as simple as ordering by LEFTX. This will traverse the tree in logical order, and also alphabetice the siblings.

Does this answer your original question? Received on Tue Oct 04 2005 - 09:03:15 CEST