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?

Maybe you should show the statement you have, the schema, the PostgreSQL version, explain what `ORDER SIBLINGS BY` for those who don't know the Oracle query language in detail, etc. You've been given this advice enough times that I don't know why you keep posting such minimal questions. — Craig Ringer, Jul 19 '13 at 02:41
Ditto. Thanks for fixing that up. BTW, 9.1.6 has a nasty security hole; see http://www.postgresql.org/support/security/faq/2013-04-04/ — Craig Ringer, Jul 19 '13 at 03:56
@CraigRinger: Since you found the updates to the Q satisfactory, you might be interested in the [new feature to **retract** a close vote](http://meta.stackexchange.com/questions/915/can-we-have-the-ability-to-rescind-a-close-vote-before-it-closes/167514#167514). Well, now that it's closed, only "reopen" is left. — Erwin Brandstetter, Jul 22 '13 at 16:17
@ErwinBrandstetter Yes, very interested. It's always annoyed me that the closevote dialog is framed in terms of the present state of a question with the implication that it might improve, but there's no way to retract a closevote or vote against a close. — Craig Ringer, Jul 23 '13 at 01:07

score 8 · Accepted Answer · edited Jun 20 '20 at 09:12

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?

How to make ORDER SIBLINGS BY?

1 Answers1

For one level of hierarchy

For any number of levels:

Linked