Skip site navigation (1) Skip section navigation (2)

Getting the right order

From: Martin Foster <martin(at)ethereal-realms(dot)org>
To: PostgreSQL Novice List <pgsql-novice(at)postgresql(dot)org>
Subject: Getting the right order
Date: 2005-10-17 03:36:29
Message-ID: 43531C3D.8010907@ethereal-realms.org (view raw or flat)
Thread:
Lists: 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(at)ethereal-realms(dot)org


pgsql-novice by date

Next:From: Magnus HaganderDate: 2005-10-17 08:21:30
Subject: Re: 8.0.3 Anoying installer error
Previous:From: Michael GlaesemannDate: 2005-10-16 23:56:16
Subject: Re: Switching from MySQL -- Missing DESCRIBE table, SHOW

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group