Parent and Child nodes from a table (navigation category table)

[shibbard]

Hi all,

I have a single table with both parent and child nodes, and each has an order number in it.

I am trying to write a single query to output them in order, its for a navigation list with categories and sub-categories.

I could manage it in code rather than in the SQL query but it would involve calling a query from within a query loop - which I want to avoid.

Table:

DBID | Title | ParentID | OrderNum
 1      aaa       0          1
 2      bbb       0          2
 3      ccc       1          1
 4      ddd       1          2
 5      eee       2          1

and I want to get a result set like:

DBID | Title | ParentID | OrderNum
 1      aaa       0          1      <<< main 
 3      ccc       1          1      <<< child
 4      ddd       1          2      <<< 2nd child
 2      bbb       0          1      <<< main 
 5      eee       2          1      <<< child

I have been looking at using a recursive SQL select or Common Table Expressions (CTE) but have not been able to figure it out yet.

Can anyone help point me in the right direction?

(Using SQL Server 2005 / ASP.Net C#)

[shibbard]

I find the solution - see the select:

declare @t table 
(DBID           int
,Title          varchar(10)
,ParentID       int
,OrderNum       int
)

insert @t
      select 1,'aaa',0,1
union select 2,'bbb',0,2
union select 3,'ccc',1,1
union select 4,'ddd',1,2
union select 5,'eee',2,1

select * 
from @t
order by ISNULL(NULLIF(ParentID,0),DBID)
        ,ParentID
        ,OrderNum
        ,DBID