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'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...