title: SQL: DELETE data from self-referencing table in specific order
body: <p>I have a table like this:</p>
  
  <pre><code>groupId guid PK
  parentId guid
  name
  left int
  right int
  </code></pre>
  
  <p>There is a foreign key from parentId to groupId (this is a self-referencing table).</p>
  
  <p>left and right are <a href="http://articles.sitepoint.com/print/hierarchical-data-database" rel="nofollow">MPTT</a> left/right values for maintaining a hierarchy. The important thing to note here is the bigger the left value, the more deeply-nested an item is (in other words: for any given item, its left value will always be bigger than the left value of all parents).</p>
  
  <hr>
  
  <p>I'm trying to write a quick DELETE statement to delete everything but the top-most group (which always has an empty GUID value), eg: </p>
  
  <pre><code>DELETE FROM [group] WHERE [groupId] &lt;&gt; '00000000-0000-0000-0000-000000000000'`
  </code></pre>
  
  <p>Unfortunately this doesn't work, as there are generally sub-groups below any given group that prevent deletion. If you run that DELETE query over and over, eventually it will delete everything, but this is obviously not a good solution.
  <br/><br/></p>
  
  <p>What I want is the equivalent of: </p>
  
  <pre><code>DELETE FROM [group] WHERE [groupId] &lt;&gt; '00000000-0000-0000-0000-000000000000' 
  ORDER BY [left] DESC
  </code></pre>
  
  <p>Of course, that isn't allowed syntax, but effectively, it should delete the items with the largest left values first to ensure there are no groups below that will prevent deletion due to FK constraints.
  <br/><br/></p>
  
  <p>I also tried:</p>
  
  <pre><code>delete from [group] where groupid in (
    select top 1000000 * from [group] 
    where groupid &lt;&gt; '00000000-0000-0000-0000-000000000000' 
    ORDER BY [left] desc
  )
  </code></pre>
  
  <p>which is valid syntax (you can only use ORDER BY if you also use TOP) but doesn't actually cause the DELETE to happen in the order of returned rows, so it still doesn't work.
  <br/><br/></p>
  
  <p>Is this possible to do, without resorting to a cursor to delete rows one-by-one?</p>