title: Recursive SQL CTE's and Custom Sort Ordering
body: <p>Image you are creating a DB schema for a threaded discussion board.  Is there an efficient way to select a properly sorted list for a given thread?  The code I have written works but does not sort the way I would like it too.</p>
  
  <p>Let's say you have this data:</p>
  
  <pre>ID   |  ParentID
  -----------------
  1    |   null
  2    |   1
  3    |   2
  4    |   1
  5    |   3</pre>
  
  <p>So the structure is supposed to look like this:</p>
  
  <pre>1
  |- 2
  |  |- 3
  |  |  |- 5
  |- 4</pre>
  
  <p>Ideally, in the code, we want the result set to appear in the following order: <b>1, 2, 3, 5, 4</b><br>
  <b>PROBLEM:</b> With the CTE I wrote it is actually being returned as: <b>1, 2, 4, 3, 5</b></p>
  
  <p>I know this would be easy to group/order by using LINQ but I am reluctant to do this in memory.  It seems like the best solution at this point though...</p>
  
  <p>Here is the CTE I am currently using:</p>
  
  <pre><code>with Replies as (   
      select c.CommentID, c.ParentCommentID 1 as Level
          from Comment c
          where ParentCommentID is null and CommentID = @ParentCommentID
  
      union all
  
      select c.CommentID, c.ParentCommentID, r.Level + 1 as Level
         from Comment c
         inner join Replies r on c.ParentCommentID = r.CommentID
  )
  
  select * from Replies
  </code></pre>
  
  <p>Any help would be appreciated;  Thanks!
  <hr /><br>
  I'm new to SQL and had not heard about hierarchyid datatype before.  After reading about it from <a href="https://stackoverflow.com/questions/202912/hierarchical-data-in-linq-options-and-performance/202929#202929">this comment</a> I decided I may want to incorporate this into my design.  I will experiment with this tonight and post more information if I have success.</p>
  
  <p><hr />
  <b>Update</b><br>
  Returned result from my sample data, using dance2die's suggestion:</p>
  
  <pre>ID  |  ParentID  | Level  | DenseRank
  -------------------------------------
  15     NULL          1         1
  20     15            2         1
  21     20            3         1
  17     22            3         1
  22     15            2         2
  31     15            2         3
  32     15            2         4
  33     15            2         5
  34     15            2         6
  35     15            2         7
  36     15            2         8</pre>