title: SQL Recursive Menu Sorting
body: <p>I've got a simple table that I'm using to represent a hierarchy of categories.</p>
  
  <pre><code>CREATE TABLE [dbo].[Categories](
      [ID] [int] IDENTITY(1,1) NOT NULL,
  [Title] [varchar](256) NOT NULL,
  [ParentID] [int] NOT NULL,
  CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED 
  (
  [ID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  
  INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('All', 0)
  INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('Banking', 8)
  INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('USAA Checking', 2)
  INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('USAA Mastercard', 2)
  INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('Medical', 8)
  INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('Jobs', 8)
  INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('Archive', 1)
  INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('Active', 1)
  INSERT INTO [MDS].[dbo].[Categories]([Title],[ParentID]) VALUES ('BoA Amex', 2)
  </code></pre>
  
  <p>Everything is fine except for selecting the entire tree. Here is my query, I removed my ORDER BY because it doesn't work:</p>
  
  <pre><code>WITH CategoryTree (ID, Title, Level, ParentID) AS
  (
     SELECT  r.ID, r.Title, 0 Level, r.ParentID
     FROM Categories r
     WHERE r.ParentID = 0
  
     UNION ALL
  
     SELECT c.ID, c.Title, p.Level + 1 AS Level, c.ParentID
     FROM Categories c
     INNER JOIN CategoryTree p
        ON p.ID = c.ParentID
  )
  
  SELECT  ID,
      REPLICATE('-----', Level) + Title AS Title,
      ParentID
  FROM CategoryTree
  </code></pre>
  
  <p>Results:</p>
  
  <pre><code>ID  Title                        ParentID
  1   All                              0
  7   -----Archive                     1
  8   -----Active                      1
  2   ----------Banking                8
  5   ----------Medical                8
  6   ----------Jobs                   8
  3   ---------------USAA Checking     2
  4   ---------------USAA Mastercard   2
  9   ---------------BoA Amex          2
  </code></pre>
  
  <p>The result I want is this:</p>
  
  <pre><code>ID  Title                            ParentID
  1   All                               0
  8   -----Active                      1
  2   ----------Banking                8
  9   ---------------BoA Amex          2
  3   ---------------USAA Checking     2
  4   ---------------USAA Mastercard   2
  6   ----------Jobs                   8
  5   ----------Medical                8
  7   -----Archive                     1
  </code></pre>
  
  <p>What is killing me is I got this working perfectly before but then I forgot to back up the DB and lost it in a server upgrade.</p>
  
  <p>I looked at the HierarchyID type in 2008 but it just seems like a big pain in the ass if you care about order of children at the same level.</p>