Re: how to order a 'branch' in a CONNECT BY query

From: Tapio Luukkanen <vtl_at_hemuli.tte.vtt.fi>
Date: 21 Aug 1994 09:47:22 GMT
Message-ID: <VTL.94Aug21124722_at_morko.tte.vtt.fi>

   dmcohen_at_eng.umd.edu (Daniel M. Cohen) writes:

> How do you order a 'branch' when using SQL's connect by clause? I
> tried to add the 'order by' clause but this order the entire query,
> not just each subset ('branch') of the rows.

Hi,

By having a concatenated index on the key-combination that expands the connect-by clause (ie. the one that returns the next-level rows) you seem to be able to return the rows in a nice order.

Don't know if the following proves anything, but here goes...

    create table foo (id number(2), p_id number(2), ord number(2));

    create index foo_i on foo(p_id, ord);

       /* when a query retrieves child rows by using index FOO_I
        * they will be returned ordered by ORD */

    insert into foo values (1, 0, 0);
    insert into foo values (2, 1, 3);
    insert into foo values (3, 1, 2);
    insert into foo values (4, 3, 0);
    insert into foo values (5, 1, 1);
    insert into foo values (6, 2, 4);
    insert into foo values (7, 2, 3);
    insert into foo values (8, 2, 2);
    insert into foo values (9, 4, 0);
    insert into foo values (10, 5, 2);

    insert into foo values (11, 5, 1);
    insert into foo values (12, 2, 1);

    column id format A20;
    select lpad(' ', 2 *LEVEL) ||TO_CHAR(id) id, p_id, ord       from foo
    connect by p_id = prior id
     start with id = 1;

    drop index foo_i;
    drop table foo;
    /

Output should be ordered by ORD within one P_ID:

    ID                         P_ID        ORD
    -------------------- ---------- ----------
      1                           0          0
        5                         1          1
          11                      5          1
          10                      5          2
        3                         1          2
          4                       3          0
            9                     4          0
        2                         1          3
          12                      2          1
          8                       2          2
          7                       2          3
          6                       2          4

Regards,

Tapio

--
! tapio luukkanen  !  vtl_at_tik.vtt.fi (130.188.52.2)
! phone: work 456 6056, home 455 1067

Received on Sun Aug 21 1994 - 11:47:22 CEST