You will have to use an ORDER BY somewhere. Not even Oracle guarantees that the results will always be returned in the same order if you don't use an ORDER BY.
Remember you are dealing with (mathematical) sets which do [bold]not[/bold] have an implicit order. A DBMS is not a spreadsheet.
Using CONNECT BY does impose an implicit order, that's why in your statement it's very likely that the sorting will stay that way in Oracle, but if you want the SQL Server result sorted, tell the server to do so.
We can find four branches:
1.the first 4 records show two branches
2.the first record and No. 5-7 record show other two branches
We can find that Parent node(0,2461) have two direct children node(2461,2463).(2461,2462). And the node information of the children node(2461,2463).(2461,2462) is sequencecd in different block.
But the results in SQL Server 2005 can not show these brances.And the results can not be sequenced in different block. I want to get the results with tree-like structure (the first branche,the second branche,and .....)in SQL Server 2005.
Please help me. If possible ,please give me your emails so that I can describe what I need in the results.
I think that what you are describing here are analtic functions native to Oracle
CONNECT BY PRIOR
To my knowledge Sql Server does not have this functionallity at this time.
The only way to accomplish what I think that you are tring to accomplish is within a SP and build your result while cursoring through recordsets.
If you have a defined/finite number og related items
ie grand parent
You can accomplish your task using you table joined to itself.
from FROM TEST lvl1
left outer join test lvl2 on lvl1.folder_node =lvl2.Parent_node
left outer join test lvl3 on lvl2.folder_node =lvl1.Parent_node
This can be done in TSQL, without using cursors, and without using recursion.
But before I go to the (not inconsiderable) trouble of explaining how, please explain WHY it is so important that the data be returned in that particular order. Data ordering is normally irrelevant to the database server, whether Oracle or MSSQL. Oracle just "happens" to return the data in that order for this particular non-standard function, so don't expect it to be a simple matter to force MSSQL to follow another engine's internal logic.
If it's not practically useful, then it's practically useless.