Re: Nested sort, trying again

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

.

Relevant Pages

  • Re: Auto-generate recently visited record-list
    ... Form Company with CompanyID as primary key and autonummered / ... Table History with LogID and FkID ... Allen Browne - Microsoft MVP. ... On the company form I added a combo list CompanyName ORDER BY Inserting ...
    (comp.databases.ms-access)
  • datasets - working with alot of data
    ... But this is more of a general question. ... the oldest million records into a history table, ... I could have made a primary key out ... of this post will not scare people off from reading it. ...
    (microsoft.public.dotnet.general)
  • Re: Navigating on forms...
    ... The Current event fires every time a different record in the form's ... whereas the Update event only fires when you make a change to the data in ... to write the current record's primary key value to the history ...
    (microsoft.public.access.forms)
  • Re: writing a query
    ... start_date DATETIME NOT NULL, ... PRIMARY KEY (ssn, start_date)); ... If you try to do this with just one date in the history, ...
    (microsoft.public.sqlserver.programming)