osullic osullic - 14 days ago 5
PHP Question

Select rows from a table and use multiple interrelated columns for ordering

I have a table that contains information about "teams". Teams can have subteams - which has been implemented by each row having a "parent_id" column that contains the "team_id" of that subteam's parent. Here's an example of the data:

team_id team_name parent_id
1 sales (null)
2 executives (null)
3 emea sales 1
4 apac sales 1
5 uk sales 3


What I'm trying to achieve is simply to select all rows in the table in order of team_id, BUT I want subteams returned right after their parent team, also in team_id order. So, for the above example data, I would want the data returned in this order:

team_id team_name parent_id
1 sales (null)
3 emea sales 1
5 uk sales 3
4 apac sales 1
2 executives (null)


I've spent several hours wondering how to achieve this, and really haven't come up with anything useful! I would appreciate any pointers on how to approach this.

Note, I am working with an existing project and can't really make drastic database/implementation changes. I'm using PHP and Oracle with the use of stored procedures, which are new to me.

Answer

You can use a hierarchical query with the ORDER SIBLINGS BY clause, like so:

WITH teams AS (SELECT 1 team_id, 'sales' team_name, NULL parent_id FROM dual UNION ALL
               SELECT 2 team_id, 'executives' team_name, NULL parent_id FROM dual UNION ALL
               SELECT 3 team_id, 'emea sales' team_name, 1 parent_id FROM dual UNION ALL
               SELECT 4 team_id, 'apac sales' team_name, 1 parent_id FROM dual UNION ALL
               SELECT 5 team_id, 'uk sales' team_name, 3 parent_id FROM dual)
SELECT team_id,
       team_name,
       parent_id
FROM   teams
CONNECT BY PRIOR team_id = parent_id
START WITH parent_id IS NULL
ORDER SIBLINGS BY team_id;

   TEAM_ID TEAM_NAME   PARENT_ID
---------- ---------- ----------
         1 sales      
         3 emea sales          1
         5 uk sales            3
         4 apac sales          1
         2 executives