Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on May 1st, 2012  |  syntax: None  |  size: 4.22 KB  |  hits: 11  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Comment threading with score factoring
  2. (01)"This is a top level comment."
  3. (02)-"This is a second level comment. A reply to the top level comment above."
  4. (06)-"This is also a second level comment / another reply to comment 01."
  5. (07)--"This is a reply to comment 06."
  6. (03)"This is a different top level comment."
  7. (05)-"This is a reply to the comment above."
  8. (08)--"This is a reply to that comment in turn."
  9. (10)---"This is a deeper comment still."
  10. (04)"This is one more top level comment."
  11. (09)-"This is one more reply."
  12.        
  13. Comment ID  | Parentage
  14.      01     |              (Comment 01 has no parent because it is top level)
  15.      02     | 01-          (Comment 02 was a reply to comment 01)
  16.      03     |
  17.      04     |              
  18.      05     | 03-
  19.      06     | 01-
  20.      07     | 01-06-       (Comment 07 has two ancestors 01 and then 06)
  21.      08     | 03-05-
  22.      09     | 04-
  23.      10     | 03-05-08-
  24.        
  25. Comment ID  | Parentage
  26.      01     |
  27.      02     | 01-
  28.      03     |
  29.      04     |              
  30.      05     | 03-
  31.      06     | 01-
  32.      07     | 01-06-
  33.      08     | 03-05-
  34.      09     | 04-
  35.      10     | 03-05-08-
  36.      11     | 03-05-08-10-
  37.        
  38. Order by CONCAT(Parentage, ID)
  39.  
  40. Comment ID  | Parentage    |   CONCAT(Parentage, ID)
  41.      01     |              |   01-
  42.      02     | 01-          |   01-02-
  43.      06     | 01-          |   01-06-
  44.      07     | 01-06-       |   01-06-07-
  45.      03     |              |   03-
  46.      05     | 03-          |   03-05-
  47.      08     | 03-05-       |   03-05-08-
  48.      10     | 03-05-08-    |   03-05-08-10-
  49.      11     | 03-05-08-10- |   03-05-08-10-11-
  50.      04     |              |   04-
  51.      09     | 04-          |   04-09-
  52.        
  53. (01)"This is a top level comment."
  54. (02)-"This is a reply to the top level comment."
  55. (06)-"This is another reply that was posted later than the first."
  56. (07)--"This is a reply to the second level comment directly above."
  57. (03)"This is a different top level comment."
  58. (05)-"This is a reply to the comment above."
  59. (08)--"This is a reply to the comment above."
  60. (10)---"This is a deeper comment still."
  61. (11)----"THIS COMMENT WAS ADDED IN THE EARLIER EXAMPLE."
  62. (04)"This is one more top level comment."
  63. (09)-"This is one more reply."
  64.        
  65. (01)"This is a top level comment." [6 votes]
  66. (02)-"This is a reply to the top level comment." [2 votes]
  67. (06)-"This is another reply that was posted later than the first." [30 votes]
  68. (07)--"This is a reply to the second level comment directly above." [5 votes]
  69. (03)"This is a different top level comment." [50 votes]
  70. (05)-"This is a reply to the comment above." [4 votes]
  71. (08)--"This is a reply to the comment above." [0 votes]
  72. (10)---"This is a deeper comment still." [0 votes]
  73. (11)----"THIS COMMENT WAS ADDED IN THE EARLIER EXAMPLE." [0 votes]
  74. (04)"This is one more top level comment." [2 votes]
  75. (09)-"This is one more reply." [0 votes]
  76.        
  77. "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."
  78.        
  79. 'CommentTree' has more columns than were specified in the column list.
  80.        
  81. ParentId  |  CommentId  |  Comment  |  Vote  | Level
  82. NULL      |      1      | Text here |   6    |  0
  83. NULL      |      3      | Text here |   50   |  0    
  84. NULL      |      4      | Text here |   2    |  0    
  85. 4         |      9      | Text here |   0    |  1    
  86. 3         |      5      | Text here |   4    |  1    
  87. 5         |      8      | Text here |   0    |  2    
  88. 8         |      10     | Text here |   0    |  3  
  89. 10        |      11     | Text here |   0    |  4    
  90. 1         |      2      | Text here |   2    |  1    
  91. 1         |      6      | Text here |   30   |  1    
  92. 6         |      7      | Text here |   5    |  2
  93.        
  94. Comment ID  |   Parent ID    |   Comment    |  Vote
  95.        
  96. WITH CommentTree (ParentId, CommentId, Comment, Vote)
  97. AS
  98. (
  99. -- Anchor member definition
  100.     SELECT c.ParentId, c.CommentId, c.Comment, c.Vote,
  101.         0 AS Level
  102.     FROM dbo.Comments AS c
  103.     WHERE ParentId IS NULL
  104.     UNION ALL
  105. -- Recursive member definition
  106.     SELECT c.ParentId, c.CommentId, c.Comment, c.Vote,
  107.         Level + 1 AS Level
  108.     FROM dbo.Comments AS c
  109.     INNER JOIN CommentTree AS d
  110.         ON c.ParentID = d.CommentID
  111.     Order by C.Vote
  112. )
  113. SELECT ParentId, CommentId, Comment, Vote FROM CommentTree
create a new version of this paste RAW Paste Data