Re: Nested sort, trying again

From: Bob Stearns <rstearns1241_at_charter.net>
Date: Thu, 29 Sep 2005 16:08:10 -0400
Message-ID: <KSX_e.17859$L45.15135@fe07.lga>

mAsterdam wrote:
> Bob Stearns wrote:
>

>> 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,

>
>
> What does namex, etc... mean? I am guessing namex
> is a (the?) name (is it globally unique?) for the location,
> rightx is a (the?) name for the location to the right of it,
> and leftx to the left. Right sofar?
>
> No abovex, belowx, behindx, infrontofx, so lineair space?
>

>> 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.

>
>
> In the example I don't see what you mean by level.
> Which level are you talking about?
>

>> If I didn't have to have  changeable names, I could define the data in 
>> alphabetic order.

>
>
> No. In a table, the rows are supposed to be unordered.
> You can present them in an order you would like to see,
> but to /define/ them in an order would be
> breaking the rules.
>
> But maybe I am not understanding your question right.
>

>> Given that I can't do that,

>
>
> Indeed you can't - not because you have to have
> changeable names, but because (we pretend)
> rows are unordered.
>

>> is there (relatively) easy
>> way to present the data in a sorted manner?

>
>
> Earlier, Hugo Kornelis wrote:
> > Sure!
> >
> > SELECT NAMEX
> > FROM IS3.LOCATIONS
> > WHERE whatever your current where clause is
> > ORDER BY NAMEX
>
> Is there something wrong with that?
>

>> 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.

>
>
> That's a different problem. One at a time.
> Let's postpone the history-problem
> for now and make sure can get all
> data we need for the current situation.
>

>> Because of the arbitrary number of levels,

>
>
> You speak of levels in your problem description,
> but there are none yet in your data.
> Maybe that is the real problem:
> "how to model a hierarchy of locations?"
>

>> 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),

>
>
> Just nitpicking: try to use positives where possible.
> If you really want this meaning say e.g. "obsolete".
>
> More nitpicking:
> - Why character(25) for Y or N?
> - No date?
>
> But, as I said, ISTM history is a problem
> we can focus on later.
>

>>     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

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:

a 1 16
b 2 7
c 3 4
d 5 6
e 8 15
f 9 14
g 10 11
h 12 13

A leaf node has left=right-1. Higher nodes have left < left of any child, right > right of any child. Lots of good properties for adding and searching, not too difficult changing, rather harder deletion.

In this case the tree is sorted in the order I want to present it to my users. Consider the tree z(y(x,w),v(y(t,s))) which would have a similar representation to the first on but which I wish to present to my users as:

The question is: Is there an easy (relatively) way to produce this sort without a recursive sql user defined function? Received on Thu Sep 29 2005 - 15:08:10 CDT