CODEWITHSUNDEEP
sqlselectoracle11g
Adam
Adam

Reputation: 5445

How can I order hierarchy trees by branch in a select statement returning all hierarchy levels?

This is my current SQL: 

SELECT filter_id, parent_id, level_id, match 
FROM t_filters
ORDER BY level_id;

It shows the following:

ID      PARENT  RANK    FILTER VALUE
10004           1       HQ
10002           1       Finance
10006           1       IT
10003   10006   2       HQ - Central
10005   10004   2       HQ - Non Core & Legacy
10001   10005   3       Non Core

My first attempt here orders the records by the hierarchy_level_id, which is basically the rank.

Note the parent field is null where the record is at the top of the hierarchy.

Also, the branch can have any length from 1 to 8 entries (8 is the current max but could theoretically increase by 1 or 2).

What I want to show is the following, which should use the parent field to collate the individual branches of the hierarchy together: 

ID      PARENT  RANK    FILTER VALUE
10004           1       HQ
10005   10004   2       HQ - Non Core & Legacy
10001   10005   3       Non Core
10002           1       Finance
10006           1       IT
10003   10006   2       HQ - Central

Upvotes: 1

Views: 194

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220807

The following solution orders siblings by id. In your comments, you've mentioned wanting to order siblings by (filter) value. Just replace the relevant expression to achieve this.

Use recursive SQL, Oracle syntax:

SELECT *
FROM t_filters
START WITH parent IS NULL
CONNECT BY parent = PRIOR id
ORDER SIBLINGS BY id

Alternatively, SQL standard syntax (the standard and some databases would require a RECURSIVE keyword, but Oracle doesn't allow it). A bit more tedious, but more extensible:

WITH /* RECURSIVE */ r (id, parent, rank, value, path) AS (
  SELECT id, parent, rank, value, '' || id
  FROM t_filters
  WHERE parent IS NULL

  UNION ALL

  SELECT f.id, f.parent, f.rank, f.value, r.path || '/' || f.id
  FROM r
  JOIN t_filters f ON r.id = f.parent
)
SELECT *
FROM r
ORDER BY path

Upvotes: 3

Related Questions