Recursive Join with Order

Question

I have a table with a list of pages, some with parent pages. These pages are in a very random order. I need to make a recursive join statement to select all pages ordered by Page_Order, where the parents are listed each followed by its child pages.

Table: Pages

Page_ID | Page_Name | Page_Parent | Page_Order
1       | User      | 2           | 2
2       | Admin     | NULL        | 2
3       | Pages     | 2           | 1
4       | Home      | NULL        | 1
5       | About     | NULL        | 3
6       | Contact   | 5           | 1

I want to select them in the following order (order the parent pages, each followed by its child pages):

Page_ID | Page_Name | Page_Parent | Page_Order
4       | Home      | NULL        | 1
2       | Admin     | NULL        | 2
3       | Pages     | 2           | 1
1       | User      | 2           | 2
5       | About     | NULL        | 3
6       | Contact   | 5           | 1

Answer 1

It's not specifically what you asked for but here is a way to achieve what you want. This does limit you too a finite number of child pages though and its not recursive.

; WITH cte AS (
  SELECT 1 Page_Id, 'User' Page_Name, 2 Page_Parent, 2 Page_Order union 
  SELECT 2, 'Admin   ', NULL, 2 union 
  SELECT 3, 'Pages   ', 2   , 1 union 
  SELECT 4, 'Home    ', NULL, 1 union 
  SELECT 5, 'About   ', NULL, 3 union 
  SELECT 6, 'Contact ', 5   , 1 
)

SELECT  Page_Id, Page_Name, Page_Parent, Page_order, x = CAST(Page_order  AS DECIMAL(18,2))
FROM    cte
WHERE   Page_Parent IS NULL
UNION
SELECT  child.Page_Id, child.Page_Name, child.Page_Parent, child.Page_order, X = CAST(parent.Page_order  AS DECIMAL(18,2)) + ISNULL(CAST(child.Page_order  AS DECIMAL(18,2))/10000, 0)
FROM    cte parent
        LEFT OUTER JOIN cte child 
                ON child.Page_Parent = parent.Page_Id
WHERE child.Page_Id IS NOT NULL
ORDER BY x