Hi all.
Take this example from src/test/regress/sql/with.sql:
WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
(-- non recursive termSELECT 1, * FROM department WHERE name = 'A'
UNION ALL
-- recursive termSELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd WHERE d.parent_department =
sd.id
)
SELECT * FROM subdepartment ORDER BY name;
level | id | parent_department | name
-------+----+-------------------+------ 1 | 1 | 0 | A 2 | 2 | 1 | B 3 | 3 |
2 | C 3 | 4 | 2 | D 4 | 6 | 4 | F
(5 rows)
If I omit "ORDER BY", is the output *guaranteed* (according to some standard) to be ordered in "hierarchy"-order,
meaningeach parent-department is always output before its respective child, or do I have to calculate a "level" column
(likein the example above) and specify "ORDER BY LEVEL" to be 100%?
I'm using WITH RECURSIVE as sub-queries to build up arrays of parents in each output-row and would like as little
overheadas possible and hence avoid unnecessary sorting.
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CTO
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Trollåsen | somebody else doing it wrong, without |
NORWAY | comment. | |
|
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+