NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

## All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Full
Abbreviated
Hidden
• #### too easy?(Score:1)

This seems obvious?

ORDER BY id=5, id=7, id=1, id=48

--
rjbs
• #### Re:(Score:2)

Is that SQL standard? The solution I've gone with is actually much simpler.

• #### Re:(Score:1)

I have never seen a SQL where that didn't work. I am also interested to see what could be much simpler than that. :)

--
rjbs
• #### Re:too easy?(Score:2)

by Ovid (2709) on 2009.08.14 11:03 (#70065) Homepage Journal

OK, a few people on Twitter have struck out on this and Abigail guessed something similar to what you and Adrian guessed.

The solution is:

ORDER BY length(path)

Turns out it's a deterministic emergent property of materialized paths. There is one caveat I realized, though. If you have multiple trees and you want to see the root nodes, you can do this:

SELECT id FROM tree WHERE id = path;

However, some people omit the ID from the path. So you'd have this (in particular, note the path for the 5 node):

node | path
-----+---------
1    | 5.7
3    | 5.62
5    | NULL
7    | 7
9    | 5.62
29   | 5.7
32   | 5
48   | 5.7.1
55   | 5.7.1
62   | 5

To get the parents, you'd have to do this:

SELECT id FROM tree WHERE id IS NULL;

But that means when sorting paths by length, length(node) sometimes returns NULL and the sort order on that is database dependent (and usually configurable). So you would need to do this to sort the ids correctly:

ORDER BY COALESCE(length(path),0))

• #### Re:(Score:1)

Seems problematic:

1.23.45.67

1.2.3.4.5

Perhaps you could count dots.

--
rjbs
• #### Re:(Score:2)

No, you can guarantee that conflicting paths won't exist as you walk up the tree.

• #### Re:(Score:1)

_"Seems problematic:"_

Nah - the problem is selecting all the parents of a child in order. The length of the path is guaranteed to increase.

• #### Re:(Score:1)

The advantage of explicitly counting dots (rather than relying on string length to always increase with the number of dots) is that it makes path-lengths comparable. Not directly applicable to this problem, but not uncommon.
• #### Re:(Score:1)

Wouldn't it be enough to just ORDER BY path?
• #### Re:(Score:2)

With the caveat above, yes, I think it might be.