title: SQL Server: CTE tree with priority
body: <p>I have some troubles how to build a query for a recursive parent-child tree in SQL Server.</p>
  
  <p>I have found some common table expression but i have to consider another parameter (the priority in each level) that i have not found from a search over the internet, so the structure of my table is like this</p>
  
  <pre><code>id - parentid - proprity
  </code></pre>
  
  <p>For example if i have this data:</p>
  
  <pre><code>1 - NULL - 2
  2 - NULL - 1
  3 - 2 - 2
  4 - 3 - 1
  5 - 2 - 0
  6 - 1 - 0
  7 - 2 - 3
  </code></pre>
  
  <p>The query must return this at right order:</p>
  
  <pre><code>-2
  --5
  --3
  ---4
  --7
  -1
  --6
  </code></pre>
  
  <p>Also this list is orderable so if any order is change I have to reset the priority at right place. Anyone has already a solution for this case with queries in SQL Server?</p>
  
  <p>Thanks in advance</p>