cte and hierarchial data, order is not what I want

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

cte and hierarchial data, order is not what I want

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-09-04 : 18:31:11

Hi,
I have a simple table:
Category
- categoryID
- parentID
- title

I want to display all the categories, and subcategories.
So something like:

1.0
-1.1
-1.1.0
-1.2
-1.3

My CTE is below, the order I am getting is not how I have described above, can someone tell me how to correct the ordering??

The order it returns is:

1.0
-1.1
-1.2
-1.3
--1.1.0
-----------

WITH MyCTE(categoryID, parentID, title)
AS
(
SELECT categoryID, parentID, title
FROM categories
WHERE categoryID = 1 -- root category

UNION ALL

SELECT c.categoryID, c.parentID, c.title
FROM categories c
INNER JOIN MyCTE cte ON (c.parentID = cte.categoryID)

)
SELECT * FROM MyCTE

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-04 : 18:58:45

Maybe this will help?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105537