Ordered result set with path enumeration
From: Dieter Nöth <dnoeth_at_gmx.de>
Date: Sun, 17 Aug 2003 22:34:20 +0200
Message-ID: <bhooo1$1kdal$1_at_ID-28204.news.uni-berlin.de>
Modifying Celko's Adjaceny List example to a table containing a transitive closure of a tree (without pathlength):
Date: Sun, 17 Aug 2003 22:34:20 +0200
Message-ID: <bhooo1$1kdal$1_at_ID-28204.news.uni-berlin.de>
Modifying Celko's Adjaceny List example to a table containing a transitive closure of a tree (without pathlength):
CREATE TABLE OrgChart
(emp CHAR(10) NOT NULL,
boss CHAR(10) NOT NULL,
PRIMARY KEY (emp, boss)
);
INSERT INTO OrgChart VALUES('Bert', 'Albert');
INSERT INTO OrgChart VALUES('Chuck', 'Albert');
INSERT INTO OrgChart VALUES('Donna', 'Albert');
INSERT INTO OrgChart VALUES('Donna', 'Chuck');
INSERT INTO OrgChart VALUES('Eddie', 'Albert');
INSERT INTO OrgChart VALUES('Eddie', 'Chuck');
INSERT INTO OrgChart VALUES('Fred', 'Albert');
INSERT INTO OrgChart VALUES('Fred', 'Chuck');
INSERT INTO OrgChart VALUES('Herbert','Albert');
INSERT INTO OrgChart VALUES('George', 'Albert');
INSERT INTO OrgChart VALUES('George', 'Chuck');
INSERT INTO OrgChart VALUES('George', 'Eddie');
Expected order by hierarchy:
Albert
Bert
Chuck
Donna
Eddie
George
Fred
Herbert
I'd like to know if there's a _single_ SQL statement to retrieve that result set?
Dieter Received on Sun Aug 17 2003 - 22:34:20 CEST