title: Recursive CTE without inner join
body: <p>I wonder if the pseudo query below is possible to be made with RTE (recursive CTE).
  The main idea is, that the last part of the query (cte3, cte4) would still have more queries (cte5,cte6,etc) but the main idea is that:</p>
  
  <pre><code>SELECT * FROM [previous dataset result]
  UNION
  SELECT * FROM [full result]
  WHERE statusId = [previous used status id] - 1
   id IS NOT IN (SELECT id FROM [previous dataset result])
  </code></pre>
  
  <p>I have tried using <code>INNER JOIN</code> as such a normal RTE but it doesn't gives the expected result. An <code>LEFT OUTER JOIN</code> with the RTE should give a correct result (theoretically) but since RTE won't accept <code>OUTER JOIN</code>'s, I'm kinda stuck.</p>
  
  <p>Here is what I am using at the moment (CTE without the recursive part):</p>
  
  <pre><code>;WITH cte AS (
      // query
  ), cte2 AS (
      SELECT * FROM cte
      WHERE WorkOrderStatusID = 8
      UNION
      SELECT * FROM cte
      WHERE SPStatus=9
  ), cte3 AS (
      SELECT * FROM cte2
      UNION
      SELECT * FROM cte
      WHERE WorkOrderStatusID=7
      AND OrderSPMovableID NOT IN (SELECT OrderSPMovableID FROM cte2)
  ), cte4 AS (
      SELECT * FROM cte3
      UNION
      SELECT * FROM cte
      WHERE WorkOrderStatusID=6
      AND OrderSPMovableID NOT IN (SELECT OrderSPMovableID FROM cte3)
  )
  SELECT * FROM cte4
  </code></pre>
  
  <p>Any help would be appreciated.</p>
  
  <p>Update:
  Sample data:</p>
  
  <pre><code>Id  WorkOrderStatusID   OrderSPMovableID 
  1       7                   12345
  2       8                   12345
  3       8                   11111
  4       6                   11111
  5       4                   23411
  6       7                   23411
  7       3                   43215
  8       1                   34567
  9       5                   76543
  </code></pre>
  
  <p>Expected Result:</p>
  
  <pre><code>Id  WorkOrderStatusID   OrderSPMovableID 
  2       8                   12345
  3       8                   11111
  6       7                   23411
  7       3                   43215
  8       1                   34567
  9       5                   76543
  </code></pre>
  
  <p>So, in short, from the complete data result, I want to get the data with highest <code>WorkOrderStatusID</code>. Due to the actual result would have around 20 fields, I found <code>group</code> to be a bit hard to use and verify, thus I am taking the Recursive CTE approach, if possible.</p>