Proper siblings sorting in nested sets model

From: Pavel Schevaev <pacha_shevaev_at_mail.ru>
Date: 9 Apr 2004 06:45:38 -0700
Message-ID: <320b3b2.0404090545.692729f1_at_posting.google.com>

Nested sets are just great and i've been using it for quite a while already.
The only inconvenience that popped up lately was that it seems like it's impossible to order node siblings by some arbitrary parameter, say, alphabetically, in sigle query without any vendor-specific stuff.

Oracle10g supports such sorting, even without nested sets(of course we can't afford it):

SELECT LEVEL, LPAD(' ',2*(LEVEL - 1)) || EMP_NAME "EMPLOYEE", EMP_ID, MGR_IDFROM EMPLOYEESTART WITH EMP_ID = 7839CONNECT BY PRIOR EMP_ID = MGR_IDORDER SIBLINGS BY EMP_NAME;

LEVEL EMPLOYEE                 EMP_ID     MGR_ID
----- -------------------- ---------- ----------
    1 KING                       7839
    2   BLAKE                    7698       7839
    3     ALLEN                  7499       7698
    3     JAMES                  7900       7698
    3     MARTIN                 7654       7698
    3     TURNER                 7844       7698
    3     WARD                   7521       7698
    2   CLARK                    7782       7839
    3     MILLER                 7934       7782
    2   JONES                    7566       7839
    3     FORD                   7902       7566
    4       SMITH                7369       7902
    3     SCOTT                  7788       7566
    4       ADAMS                7876       7788

(if above ascii art is messed up here's the link http://www.dbazine.com/mishra3.shtml)

Probably there's someone who faced the same problem already and got a solution? Received on Fri Apr 09 2004 - 15:45:38 CEST