How to make ORDER SIBLINGS BY?

common-table-expression, postgresql, sql, sql-order-by

Solution

This would achieve what you describe:

For one level of hierarchy

WITH RECURSIVE t AS (
   SELECT emp_id As top_id
        , emp_id
        , manager_id
        , emp_name
   FROM   recursive_test
   WHERE  manager_id = 0

   UNION ALL
   SELECT t.top_id
        , a.emp_id
        , a.manager_id
        , a.emp_name
   FROM   recursive_test a
   JOIN   t ON a.manager_id = t.emp_id
   )
SELECT emp_id
     , manager_id
     , emp_name
FROM   t
ORDER  BY top_id, emp_id;

It seems like you want to order by `emp_id` secondary ..

For any number of levels:

WITH RECURSIVE t AS (
   SELECT ARRAY[emp_id] AS hierarchy
        , emp_id
        , manager_id
        , emp_name
   FROM   recursive_test
   WHERE  manager_id = 0

   UNION ALL
   SELECT t.hierarchy || a.emp_id
        , a.emp_id
        , a.manager_id
        , a.emp_name
   FROM   recursive_test a
   JOIN   t ON a.manager_id = t.emp_id
   )
SELECT emp_id
     , manager_id
     , emp_name
FROM   t
ORDER  BY hierarchy;

This one collects ancestors (including self) in an array and sorts by it, achieving an ordering like in a table of contents.

Sorting by an array works as expected. Related:

- How to ORDER BY typical software release versions like X.Y.Z?

Problem

I'm using PostgreSQL 9.1.6 and trying to build recursive SQL. I want to sort like `ORDER SIBLINGS BY` in SQL-Server does it. Editor's note: This is probably supposed to refer to Oracle, where `ORDER SIBLINGS BY` actually exists. Test table: ``` create table RECURSIVE_TEST( EMP_ID int, MANAGER_ID int, EMP_NAME varchar(30) ); insert into recursive_test values (1 ,0 ,'MANAGER1'), (2 ,0 ,'MANAGER2'), (3 ,0 ,'MANAGER3'), (4 ,0 ,'MANAGER4'), (5 ,1 ,'emp1'), (6 ,3 ,'emp2'), (7 ,4 ,'emp3'), (8 ,2 ,'emp4'), (9 ,2 ,'emp5'), (10 ,3 ,'emp6'), (11 ,4 ,'emp7'), (12 ,3 ,'emp8'), (13 ,4 ,'emp9'), (14 ,2 ,'emp10'), (15 ,2 ,'emp11'), (16 ,1 ,'emp12'); ``` Query: ``` WITH RECURSIVE T AS ( SELECT A.EMP_ID ,A.MANAGER_ID ,A.EMP_NAME FROM RECURSIVE_TEST A WHERE MANAGER_ID = 0 UNION ALL SELECT A.EMP_ID ,A.MANAGER_ID ,A.EMP_NAME FROM RECURSIVE_TEST A, T WHERE A.MANAGER_ID = T.EMP_ID ) SELECT * FROM T; ``` Result: ``` emp_id | manager_id | emp_name --------+------------+---------- 1 | 0 | MANAGER1 2 | 0 | MANAGER2 3 | 0 | MANAGER3 4 | 0 | MANAGER4 5 | 1 | emp1 6 | 3 | emp2 7 | 4 | emp3 8 | 2 | emp4 9 | 2 | emp5 10 | 3 | emp6 11 | 4 | emp7 12 | 3 | emp8 13 | 4 | emp9 14 | 2 | emp10 15 | 2 | emp11 16 | 1 | emp12 ``` I want to sort the result-set up there like below. ``` emp_id | manager_id | emp_name --------+------------+---------- 1 | 0 | MANAGER1 5 | 1 | emp1 16 | 1 | emp12 2 | 0 | MANAGER2 8 | 2 | emp4 9 | 2 | emp5 14 | 2 | emp10 15 | 2 | emp11 3 | 0 | MANAGER3 6 | 3 | emp2 10 | 3 | emp6 12 | 3 | emp8 4 | 0 | MANAGER4 7 | 4 | emp3 11 | 4 | emp7 13 | 4 | emp9 ``` Any advice?

Original source