CONNECT BY 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 creation_time order by,it inserts new row(at 1st level) after the 3rd row( in result above) instead of first row.

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

please suggest a solution

thanks,
satheesh.

[cmmitch]

Unfortunately, Oracle does not support this feature. I sure wish it did.

There are workarounds, typically involving writing a recursive procedure to do the job, but then you end up having to do multiple SELECTs when it would be nice to do just one.

Mitchell