title: CTE with DELETE - Alternative for SQL Data Warehouse
body: <p>I would like to delete all rows in a table where the batchId (a running number) older than the previous two. I could probably do this in a SQL Database with the query:</p>
  
  <pre><code>WITH CTE AS(
      SELECT
          *,
          DENSE_RANK() OVER(ORDER BY BATCHID DESC) AS RN
      FROM MyTable
  )
  DELETE FROM CTE WHERE RN&gt;2
  </code></pre>
  
  <p>But the same is not allowed in a SQL Data Warehouse per <a href="https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017#features-and-limitations-of-common-table-expressions-in--and-" rel="nofollow noreferrer">this</a>. Looking for alternatives here.</p>