Re: Proper siblings sorting in nested sets model

From: --CELKO-- <joe.celko_at_northface.edu>
Date: 25 Apr 2004 18:25:00 -0700
Message-ID: <a264e7ea.0404251725.6091bb7a@posting.google.com>

>> Nested sets .. seems like it's impossible to order node siblings by
some arbitrary parameter .. <<

I'd convert a nested sets model into an adjacency list model and add the arbitrary sort parameter, something like this:

SELECT B.emp AS boss, E.emp, F1.arbitary_parm   FROM (OrgChart AS E

        LEFT OUTER JOIN
        OrgChart AS B
        ON B.lft
           = (SELECT MAX(lft)
                FROM OrgChart AS S
               WHERE E.lft > S.lft
                 AND E.lft < S.rgt))
       INNER JOIN
       SELECT F1.arbitary_parm
         FROM Foobar AS F1
        WHERE {{ something with B.emp and/or E.emp }}

 ORDER BY F1.arbitary_parm; Received on Sun Apr 25 2004 - 20:25:00 CDT