Getting the right order - Mailing list pgsql-novice

There are times when I feel like I'm experiencing a relapse in query
building and design.    Essentially, what I have is a fairly simple
table designed to keep track of layers, their parents, children and what
level of depth they have.   The example is below:

CREATE TABLE PGSCHEMA.Layer (
   LayerID              INT           NOT NULL DEFAULT
NEXTVAL('PGSCHEMA.seqLayer'),
   GalleryID            INT           NOT NULL,
   LayerShort           VARCHAR(16),
   LayerFull            VARCHAR(100),
   LayerDescription     VARCHAR(250),
   LayerHighlight       VARCHAR(30),
   LayerThumbnail       VARCHAR(200),
   LayerLevel           SMALLINT      NOT NULL DEFAULT '0',
   LevelZero            INT           NOT NULL DEFAULT '0',
   LevelOne             INT           NOT NULL DEFAULT '0',
   LevelTwo             INT           NOT NULL DEFAULT '0',
   LevelThree           INT           NOT NULL DEFAULT '0',
   LevelFour            INT           NOT NULL DEFAULT '0',
   LevelFive            INT           NOT NULL DEFAULT '0',
   CONSTRAINT pkLayer                 PRIMARY KEY (LayerID, GalleryID),
   CONSTRAINT fkLayer                 FOREIGN KEY (GalleryID)
     REFERENCES PGSCHEMA.Gallery (GalleryID)
       ON DELETE CASCADE
       ON UPDATE CASCADE
) WITHOUT OIDS;

By ordering on the last seven rows of information, I can instantly
organize everything by parentage, level and depth.   The only problem I
have on this matter is that it's not exactly what I am looking for.

Sample data follows:

  layerid | galleryid | layershort  | layerlevel | levelzero | levelone
| leveltwo | levelthree | levelfour | levelf
ive
--------------------------------------------------------------
56 | 271 | Wolfe            | 1 | 46 | 56 | 56 | 56 | 56 | 56
66 | 271 | Vimy             | 3 | 46 | 63 | 65 | 66 | 66 | 66
67 | 271 | McNaughton       | 3 | 46 | 63 | 65 | 67 | 67 | 67
68 | 271 | Barrifield       | 3 | 46 | 63 | 65 | 68 | 68 | 68
65 | 271 | Base             | 2 | 46 | 63 | 65 | 65 | 65 | 65
79 | 271 | Downtown         | 2 | 46 | 63 | 79 | 79 | 79 | 79
80 | 271 | End_of_Day       | 2 | 46 | 63 | 80 | 80 | 80 | 80
70 | 271 | Bellevue         | 3 | 46 | 63 | 69 | 70 | 70 | 70
71 | 271 | CE               | 3 | 46 | 63 | 69 | 71 | 71 | 71
72 | 271 | General_Hospital | 3 | 46 | 63 | 69 | 72 | 72 | 72
73 | 271 | Marine           | 3 | 46 | 63 | 69 | 73 | 73 | 73
74 | 271 | Murney           | 3 | 46 | 63 | 69 | 74 | 74 | 74
75 | 271 | Fort_Henry       | 3 | 46 | 63 | 69 | 75 | 75 | 75
51 | 271 | Boldt_castle     | 2 | 46 | 50 | 51 | 51 | 51 | 51
48 | 271 | Belle_Dairy      | 2 | 46 | 47 | 48 | 48 | 48 | 48
60 | 271 | Sharbot          | 1 | 46 | 60 | 60 | 60 | 60 | 60
87 | 271 | RMC              | 3 | 46 | 63 | 65 | 87 | 87 | 87
76 | 271 | Prison           | 3 | 46 | 63 | 69 | 76 | 76 | 76
78 | 271 | Pump_House       | 3 | 46 | 63 | 69 | 78 | 78 | 78
63 | 271 | Kingston         | 1 | 46 | 63 | 63 | 63 | 63 | 63
82 | 271 | Ghosts_Fort      | 2 | 46 | 63 | 82 | 82 | 82 | 82
83 | 271 | HMCS_Toronto     | 2 | 46 | 63 | 83 | 83 | 83 | 83
81 | 271 | Fall_View        | 2 | 46 | 63 | 81 | 81 | 81 | 81
50 | 271 | Gananoque        | 1 | 46 | 50 | 50 | 50 | 50 | 50
47 | 271 | Belleville       | 1 | 46 | 47 | 47 | 47 | 47 | 47
61 | 271 | East_kg          | 1 | 46 | 61 | 61 | 61 | 61 | 61
53 | 271 | Napanee          | 1 | 46 | 53 | 53 | 53 | 53 | 53
59 | 271 | Presquile        | 1 | 46 | 59 | 59 | 59 | 59 | 59
62 | 271 | West_kg          | 1 | 46 | 62 | 62 | 62 | 62 | 62
57 | 271 | Upper_Canada     | 1 | 46 | 57 | 57 | 57 | 57 | 57
58 | 271 | Trenton          | 1 | 46 | 58 | 58 | 58 | 58 | 58
77 | 271 | PWOR             | 3 | 46 | 63 | 69 | 77 | 77 | 77
69 | 271 | Museums          | 2 | 46 | 63 | 69 | 69 | 69 | 69
84 | 271 | Mills_Locks      | 2 | 46 | 63 | 84 | 84 | 84 | 84
85 | 271 | Misc             | 2 | 46 | 63 | 85 | 85 | 85 | 85
86 | 271 | Ontario_West     | 2 | 46 | 63 | 86 | 86 | 86 | 86
88 | 271 | Thousand_Islands | 2 | 46 | 63 | 88 | 88 | 88 | 88
89 | 271 | Water_Treatment  | 2 | 46 | 63 | 89 | 89 | 89 | 89
46 | 271 | Ontario          | 0 | 46 | 46 | 46 | 46 | 46 | 46
64 | 271 | Prince_Edward    | 1 | 46 | 64 | 64 | 64 | 64 | 64
49 | 271 | Belle_Glanmore   | 2 | 46 | 47 | 49 | 49 | 49 | 49
54 | 271 | Hell_Holes       | 2 | 46 | 53 | 54 | 54 | 54 | 54

As you can see the way children are identified from parents is that a
child will carry the information of the parent up to its level.  From
that point on the child's identifier is placed along the lines.

This will grant me the ability to use one query to instantly stack
everything in the proper order without any real modifications or
handling.    This also means that the primary keys do not have to change
when a child is added into the mix for example.

However the order comes up as this when sorted:

◦ Ontario
  ◦ Belleville
   ◦ Belle_Dairy
   ◦ Belle_Glanmore
  ◦ Gananoque
   ◦ Boldt_castle
  ◦ Napanee
   ◦ Hell_Holes
  ◦ Wolfe
  ◦ Upper_Canada
  ◦ Trenton
  ◦ Presquile
  ◦ Sharbot
  ◦ East_kg
  ◦ West_kg
  ◦ Kingston
   ◦ Base
    ◦ Vimy
    ◦ McNaughton
    ◦ Barrifield
    ◦ RMC
   ◦ Museums
    ◦ Bellevue
    ◦ CE
    ◦ General_Hospital
    ◦ Marine
    ◦ Murney
    ◦ Fort_Henry
    ◦ Prison
    ◦ PWOR
    ◦ Pump_House
   ◦ Downtown
   ◦ End_of_Day
   ◦ Fall_View
   ◦ Ghosts_Fort
   ◦ HMCS_Toronto
   ◦ Mills_Locks
   ◦ Misc
   ◦ Ontario_West
   ◦ Thousand_Islands
   ◦ Water_Treatment
  ◦ Prince_Edward

Which is correct except that it would be better if items at the root and
within the same branch were in alphabetical order.    Anyone have ideas?

    Martin Foster
    Creator/Designer Ethereal Realms
    martin@ethereal-realms.org