CODEWITHSUNDEEP
sqloracle-databasesql-order-by
jfly
jfly

Reputation: 8010

sort a table while keeping the hierarchy of rows

I have a table which represents the hierarchy of departments:

+-----------+--------------+--------------+--------------+-----------+-------+
| Top Dept. | 2-tier Dept. | 3-tire Dept. | 4-tier Dept. |    name   |  tier |
+-----------+--------------+--------------+--------------+-----------+-------+
|        00 |              |              |              |    abc    |   0   |
|           | 00-01        |              |              |    bcd    |   1   |
|           |              | 00-01-01     |              |    cde    |   2   |
|           |              | 00-01-02     |              |    abc    |   2   |
|           | 00-02        |              |              |    aef    |   1   |
|           |              | 00-02-01     |              |    qwe    |   2   |
|           |              | 00-02-03     |              |    abc    |   2   |
|           |              |              | 00-02-03-01  |    abc    |   3   |
+-----------+--------------+--------------+--------------+-----------+-------+

now I want to sort the rows which are in the same tier by their names while keeping the hierarchy overall, That's what I expect:

+-----------+--------------+--------------+--------------+-----------+-------+
| Top Dept. | 2-tier Dept. | 3-tire Dept. | 4-tier Dept. |    name   |  tier |
+-----------+--------------+--------------+--------------+-----------+-------+
|        00 |              |              |              |    abc    |   0   |
|           | 00-02        |              |              |    aef    |   1   |
|           |              | 00-02-03     |              |    abc    |   2   |
|           |              | 00-02-01     |              |    qwe    |   2   |
|           | 00-01        |              |              |    def    |   1   |
|           |              | 00-01-02     |              |    abc    |   2   |
|           |              | 00-01-01     |              |    cde    |   2   |
|           |              |              | 00-02-03-01  |    abc    |   3   |
+-----------+--------------+--------------+--------------+-----------+-------+

the missing data means null, I'm using Oracle DB, can anyone help me?
EDIT: Actually, it's a simple version of this sql, I've tried to add a new column which concats the values of the first four columns and then order by it and by name, but it did't work.

Upvotes: 0

Views: 93

Answers (1)

xQbert
xQbert

Reputation: 35343

Update: This appears to be working... SQL Fiddle

All that was really needed from my original comment was to amend name to department in that order in both selects. This allows the engine to sort by name first, while maintaining the hierarchy.

WITH cte(Dept, superiorDept, name, depth, sort)AS (
SELECT 
  Dept,
  superiorDept,
  name,
  0,
  name|| dept
FROM hierarchy h
WHERE superiorDept IS NULL

UNION ALL

SELECT 
  h2.Dept,
  h2.superiorDept,
  h2.name,
  cte.depth + 1,
  cte.sort || h2.name ||h2.dept
FROM hierarchy h2
INNER JOIN cte ON h2.superiorDept = cte.Dept
)

SELECT 
  CASE WHEN depth = 0 THEN Dept END AS 一级部门,
  CASE WHEN depth = 1 THEN Dept END AS 二级部门,
  CASE WHEN depth = 2 THEN Dept END AS 三级部门,
  CASE WHEN depth = 3 THEN Dept END AS 四级部门,
  name,
  depth,
  sort
FROM cte
ORDER BY sort, name

Upvotes: 1

Related Questions