Databases
StackOverflow2013
Postsdbo
PORecursive SQL CTE's and Custom Sort Ordering
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORecursive SQL CTE's and Custom Sort Ordering
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload