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.


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#)

8 Years
Discussion Span
Last Post by 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)

Edited by shibbard: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.