Re: Nested sort, trying again

From: Bob Stearns <rstearns1241_at_charter.net>
Date: Tue, 04 Oct 2005 03:24:27 -0400
Message-ID: <U4q0f.884$s93.346_at_fe07.lga>

David Cressey wrote:
> "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?
>
>
>
>
>
>
>
>
Yes. It means for every insert, rather than the 'natural' process, I must possibly reorganize the tree's representation as well as when any namex value changes. This is probably the best solution, even though it complicates the maintenance a bit. I'll work on the problem. Thanks. Received on Tue Oct 04 2005 - 09:24:27 CEST