Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> SQL question in Connect By...

SQL question in Connect By...

From: Diviner <diviner_at_hknet.com>
Date: Thu, 21 Jun 2001 17:09:40 +0800
Message-ID: <3B31B9D4.143C8088@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.
Received on Thu Jun 21 2001 - 04:09:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US