-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|