Complex query?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Complex query?

  1. #1
    Join Date
    Jan 2001
    Posts
    14

    Question

    Hi,

    I have a table with following columns and values:

    Table: forum

    ID | ParentID | Creation_Time | category_id

    1 | 0 | 2/1/1 | -8000
    2 | 1 | 2/2/1 | 0
    3 | 2 | 2/3/1 | 0
    4 | 1 | 2/4/1 | 0
    5 | 3 | 2/5/1 | 0
    6 | 0 | 2/5/1 | -8000
    7 | 0 | 2/6/1 | -8000
    8 | 6 | 2/7/1 | 0


    And I used a query to fetch this tree structure like :

    SELECT PRIOR PARENTID,ID,CREATION_TIME,LEVEL
    FROM FORUM
    START WITH PARENTID=0
    CONNECT BY PRIOR OID=PARENTID;

    Result:

    ID | ParentID | Creation_Time | category_id

    1 | 0 | 2/1/1 | -8000
    2 | 1 | 2/2/1 | 0
    4 | 1 | 2/4/1 | 0
    3 | 2 | 2/3/1 | 0
    5 | 3 | 2/5/1 | 0
    6 | 0 | 2/5/1 | -8000
    8 | 6 | 2/7/1 | 0
    7 | 0 | 2/6/1 | -8000



    It fetches the rows in tree structure, but i need them order by creation_time desc with group by the tree structure. But if I used the order by creation_time desc this qry fetches the rows in reverse tree structure.
    Also, without ordering by creation_time ,it inserts new row(at 1st level) after the 3rd row( in result above) instead of first row.( if i insert a row, it will be queried by this query for display - 8 , 0, 2/11/1 , -8000 should be fetched as a first row in the query).

    The query should fetch the rows
    order by creation_time desc and grouped by tree.

    please suggest a solution

    thanks,
    satheesh.


  2. #2
    Join Date
    Sep 2000
    Posts
    3
    Hi Satish did you define creation_time as a 'DATE' field ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width