Oh my. KSeet... allow me to apologize for 1.2 million members. You did absolutely everything right in posting table structure, data, desired results, etc, etc, and, still, no one helped unless you believed in the spam and bought a book.

Joe Celko is correct... your data is an "Adjaceny List". He's also correct about "Nested Set" hierarchies being a bit easier to calculate with. There's another type of hierarchy called by many names (sometimes bad names in frustration if someone doesn't know how to work them :-P) but I usually refer to them as "Hierarchical Path" or "Sort Path". Each type has advantages and disadvantages depending on what you need to do. Note that "Hierarchical Path" is nearly identical to the HierarchyID in SQL Server 2008. I just don't have it on the box I'm working from today but building it takes code nearly identical to what I have below.

For the simple task of sorting in the correct order, we'll use the "Hierarchical Path" method. To build it, we'll use a "Recursive CTE". Look that up in Books Online for a better understanding of them. I will tell you right now that they are NOT a panacea. Hierarchies are one of the few places where I don't mind the use of recursive CTE's because they're normally RBAR code. In the example that follows, the recursive CTE is "layered" or (as Celko calls it), "lasagna" code. It processes a full set (one full level) of information for each iteration which makes it non-RBAR code. It's still a bit slower than a more lengthy method but it takes a huge hierarchy to really appreciate the difference so the recursive CTE is usually good enough.

Here's both your test data setup and the code to produce the output you asked for... I also modified the code to indent the items by level. You can, of course, easily remove that.

-- DROP TABLE #tblAsset;

CREATE TABLE #tblAsset(ID int IDENTITY(1,1),AssetID bigint,ParentID bigint,Asset varchar(90),Node int);

INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node)VALUES(3319,3214,'Apartment',1);

INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3320,3319,'Building 1508',2);

INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3321,3319,'Building 1509',2);

INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3322,3319,'Building 1510',2);

INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES(3323,3320,'Room 210',3);

INSERT INTO #tblAsset(AssetID, ParentID,Asset,Node) VALUES( 3324,3320,'Room 211',3);

WITH

cteBuildPath AS

(--==== This is the "anchor" part of the recursive CTE

SELECT anchor.AssetID,

anchor.ParentID,

anchor.Asset,

anchor.Node,

1 AS HLevel,

CAST(CAST(anchor.AssetID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath

FROM #tblAsset AS anchor

WHERE Node = 1

UNION ALL -------------------------------------------------------------------

--==== This is the "recursive" part of the CTE that adds 1 for each level

-- and concatenates each level of AssetID's to the SortPath column.

SELECT recur.AssetID,

recur.ParentID,

recur.Asset,

recur.Node,

cte.HLevel + 1 AS HLevel,

CAST(cte.SortPath + CAST(Recur.AssetID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath

FROM #tblAsset AS recur

INNER JOIN cteBuildPath AS cte

ON cte.AssetID = recur.ParentID

)--==== This final INSERT/SELECT creates the "narrow surrogate" column (NodeNumber)

SELECT AssetID, ParentID, Asset = SPACE((Hlevel-1)*4)+Asset, Node --,SortPath

FROM cteBuildPath

ORDER BY SortPath

;

Here's the result...

AssetID ParentID Asset Node

-------------------- -------------------- ------------------------------ -----------

3319 3214 Apartment 1

3320 3319 Building 1508 2

3323 3320 Room 210 3

3324 3320 Room 211 3

3321 3319 Building 1509 2

3322 3319 Building 1510 2

One final thing... notice the hierarchy level I calculated... it turns out to be the same as "Node". I don't know if "Node" was intended to be the "level", but it sure does look that way.

Again... I'm not sure what happened and why no one reached out to help especially since you followed ALL the "rules" of posting on your very first post.