How to make ORDER SIBLINGS BY?

Question

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?

Answer 1

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?