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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match