Re: Connect By Challenge!

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9ksiu602jia_at_drn.newsguy.com>...
> In article <966fcac1.0108081223.39098cc_at_posting.google.com>, akm111_at_yahoo.com
> says...
> >
> >I'd love some help with this.
> >
> >Let's say I've got a family tree type of database with:
> >
> > person, parent, older_sibling
> >
> >as columns, and I want to output this data in a tree structure. By
> >using:
> >
> > SELECT person, parent, older_sibling
> > FROM table_name
> > START WITH parent = 'Eve'
> > CONNECT BY parent = PRIOR person
> >
> >I get a tree structure, but the siblings aren't in any particular
> >order. I've read that Oracle 9 now has an ORDER SIBLINGS BY option,
> >but for now I'm stuck with Oracle 8.
> >
> >Can anyone steer me in the right direction to get the siblings in
> >order?
> >
> >Thanks a million,
> >Amy
>
> yes, 9i has the order siblings by that will solve this once and for all.
>
> Until then, if you have total control over the indexing scheme -- a concatenated
> index on (parent,older_sibling) -- without any other index with parent on the
> leading edge -- will probably do it (by accident)
>
> see
>
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:9212348049

Thanks, Tom. You're a god.

I realize now, though, that I wasn't thinking about it exactly right, and I think I led you astray a bit. What you suggest would work if I wanted the siblings in alphabetical order, but what I need is actually more complicated than that. They need to be ordered by age, but we don't know their birthdate, just who their previous sibling is.

Any thoughts on how to approach that?

TIA,
Amy Received on Thu Aug 09 2001 - 10:59:16 CDT