Stories
Slash Boxes
Comments
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
More | Login | Reply
Loading... please wait.
  • by rjbs (4671) on 2009.08.14 10:43 (#70060) Homepage Journal

    This seems obvious?

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

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

      • 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
        • 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 |

          • Seems problematic:

            1.23.45.67

            1.2.3.4.5

            Perhaps you could count dots.

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

            • _"Seems problematic:"_

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

            • 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.
          • Wouldn't it be enough to just ORDER BY path?