Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

2 Answers
2

The problem is you need to extract the sort order for the root items and make that same order apply to the sub-items in the tree without re-ordering the sub-items. In Oracle you can do this with a windowing function as follows:

I don't know if you can do something like that in MYSQL, so here is a version using a GROUP BY and self join that works in MySQL 5.5.28.

SELECT Label
FROM T1 a
JOIN
(SELECT SUBSTR(Path,2,1) FirstLevel, MIN(Sort_Order) FirstSort FROM T1
WHERE Label IS NOT NULL GROUP BY SUBSTR(Path,2,1)) b
ON substr(a.Path,2,1) = b.FirstLevel
ORDER BY FirstSort, Path;

It appears that using Sort Order in any combination just to accommodate a particular level is just not going to cut it in this instance. I believe what you are looking for is an in-order tree traversal.