Sorting a tree while maintaining structure

Question

I have a tree using materialized path.

The table is similar to:

+-------------+--------+----------+-------+
| path        | depth  | children | score |
+-------------+--------+----------+-------+
| 0001        | 1      | 3        | 5     |
| 00010001    | 2      | 0        | -3    |
| 00010002    | 2      | 0        | 27    |
| 00010003    | 2      | 0        | 10    |
| 0002        | 1      | 2        | 12    |
| 00020001    | 2      | 0        | 0     |
| 00020002    | 2      | 1        | 3     |
| 00020002001 | 3      | 0        | 1     |
+-------------+--------+----------+-------+

I want to sort by the score column while maintaining the tree structure.
What matters is children are beneath their parents.

+-------------+--------+----------+-------+
| path        | depth  | children | score |
+-------------+--------+----------+-------+
| 0002        | 1      | 2        | 12    |
| 00020002    | 2      | 1        | 3     |
| 00020002001 | 3      | 0        | 1     |
| 00020001    | 2      | 0        | 0     |
| 0001        | 1      | 3        | 5     |
| 00010002    | 2      | 0        | 27    |
| 00010003    | 2      | 0        | 10    |
| 00010001    | 2      | 0        | -3    |
+-------------+--------+----------+-------+

The path column is only used in the database, so it doesn't have to be sequential.

The SQL I currently use to sort the tree so I can build it:

SELECT path, depth, children, score FROM mytable ORDER BY path ASC

You could do an ORDER BY over multiple fields, sorting by path and score. — Mikola, Jun 25 '11 at 00:43
in your example required output, paths are shown descending, yet you state 'ASC' ? — Mitch Wheat, Jun 25 '11 at 01:03
As an aside, you could use an array for `path` so that you wouldn't have to worry about the zero-padding or four digit limitation. — mu is too short, Jun 25 '11 at 02:30
If in this particular example the `00020001` item had the score of `2`, would it still have to retain the same position in the output? — Andriy M, Jun 25 '11 at 13:31

Denis de Bernardy · Accepted Answer · 2011-06-25T10:48:26.683

You're going to need a recursive query and a window function. It'll look similar to this:

with recursive
ordered_tree as (
select tree.*,
       array[row_number() over w] as score_path
from   tree
where  depth = 1
window w as (order by tree.score desc)
union all
select tree.*,
       parent.score_path || array[row_number() over w] as score_path
from   tree
join   ordered_tree as parent on parent.id = tree.parent_id
window w as (partition by tree.parent_id order by tree.score desc)
)
select *
from   ordered_tree
order by score_path

Note: the above will be rather slow if your set is large...

Sorting a tree while maintaining structure

1 Answers1