Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> SQL question in Connect By...
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.Received on Thu Jun 21 2001 - 04:09:40 CDT