title: SQL Server: join on derived table that contains WITH clause?
body: <p>I'd like to join on a subquery / derived table that contains a WITH clause (the WITH clause is necessary to filter on <code>ROW_NUMBER() = 1</code>).  In Teradata something similar would work fine, but Teradata uses <code>QUALIFY ROW_NUMBER() = 1</code> instead of a WITH clause.</p>
  
  <p>Here is my attempt at this join:</p>
  
  <pre><code>-- want to join row with max StartDate on JobModelID
  INNER JOIN (
      WITH AllRuns AS (
          SELECT *,
              ROW_NUMBER() OVER (PARTITION BY JobModelID ORDER BY StartDate DESC) AS RowNumber
          FROM Runs
      )
      SELECT * FROM AllRuns WHERE RowNumber = 1
  ) Runs
  ON JobModels.JobModelID = Runs.JobModelID
  </code></pre>
  
  <p>What am I doing wrong?</p>