Postgres Pro
Downloads
Home   >   mailing lists

Thread: Order of "WITH RECURSIVE" output

Order of "WITH RECURSIVE" output

From
Andreas Joseph Krogh
Date:
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 |                                             |
------------------------+---------------------------------------------+

Re: Order of "WITH RECURSIVE" output

From
Tom Lane
Date:
Andreas Joseph Krogh <andreak@officenet.no> writes:
> If I omit "ORDER BY", is the output *guaranteed* (according to some
> standard) to be ordered

No.  It's not necessary to read any other details to answer that ...
        regards, tom lane

Re: Order of "WITH RECURSIVE" output

From
Andreas Joseph Krogh
Date:
On Tuesday 07 July 2009 03:55:35 am Tom Lane wrote:
> Andreas Joseph Krogh <andreak@officenet.no> writes:
> > If I omit "ORDER BY", is the output *guaranteed* (according to some
> > standard) to be ordered
>
> No.  It's not necessary to read any other details to answer that ...
>
>             regards, tom lane

Ok, thanks for prompt reply.

--
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 |                                             |
------------------------+---------------------------------------------+