Satish Jha (5/24/2006)
Thanks for this article. I have one question here -how can I sort the result from CTE in hierarchical order as well as siblings on some orther order say all records of one level by firstname. In the article the example only sorts it ib hierarchical order
When you select from the CTE, you can use any of the usual structures for select statements. You can use aggregates (sum, count, et al), you can use joins, you can use Where clauses, Group By, Having, and Order By.
Just treat the CTE the same way you would any other derived table.
For example:
;with HierarchyCTE (Lvl, ID, ParentID, Name) as
(select 1, ID, ParentID, Name
from dbo.Hierarchy
union all
select Lvl + 1, h2.ID, h2.ParentID, h2.Name
from dbo.Hierarchy h2
inner join HierarchyCTE
on h2.ParentID = HierarchyCTE.ID)
select Lvl, ID, ParentID, Name
from HierarchyCTE
order by Name
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon