Re: SQL question in Connect By...

the connect by query looks something like that (depends which data you want):

select RefNo,ParentNode,MsgDate,Topic,level from Msg

    start with RefNo=1
    connect by prior RefNo=ParentNode

the values "level" gives you the depth of the tree in the parent-child relation the selects starts with the refno=1
and connects the prior refno with the parentnode value of the actual row

Michel Cadot wrote:

> "Diviner" <diviner_at_hknet.com> a écrit dans le message news: 3B31B9D4.143C8088_at_hknet.com...
> > I want to query a record-set from a self-relationship table. The
> > table stores records sound like email/newsgroup messages. It
> > has thread by thread stories inside. The table structure like
> > this:
> > Create Table Msg(
> > RefNo integer primary key,
> > ParentNode integer, -- link to prior RefNo
> > MsgDate Date default sysdate not null,
> > Topic varchar(60) not null
> > );
> >
> > The tree structure is totally composed by RefNo and ParentNode
> > two fields. If the record is a head node in the message thread,
> > ParentNode leave NULL.
> >
> > Now I want to list out all the message thread but don't know how
> > to control the Ordering of the selected records. What I want the
> > order list out is, message thread is ordered by descending MsgDate
> > order, but of cause each replied message should be always follow
> > by its ParentNode no matter what is its MsgDate.
> >
> > So how is the SQL? Thank you very much.
> >
> > --
> > Best regards,
> > Diviner.
> >
>
> Have a look at AskTom site, url:
> http://asktom.oracle.com/pls/ask/f?p=4950:8:128829::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:9212348
> 049,{connect}
>
> --
> Have a nice day
> Michel
Received on Fri Jun 22 2001 - 02:46:42 CDT