Complex query?

[bsatheesh]

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.

[apparaov]

Hi Satish did you define creation_time as a 'DATE' field ?