SQL Server: join on derived table that contains WITH clause?

Question

I'd like to join on a subquery / derived table that contains a WITH clause (the WITH clause is necessary to filter on ROW_NUMBER() = 1). In Teradata something similar would work fine, but Teradata uses QUALIFY ROW_NUMBER() = 1 instead of a WITH clause.

Here is my attempt at this join:

-- 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

What am I doing wrong?

Answer 1

Adding a join condition is probably less efficient, but usually works fine for me.

INNER JOIN (
    SELECT *,
           ROW_NUMBER() OVER 
           (PARTITION BY JobModelID 
           ORDER BY StartDate DESC) AS RowNumber
      FROM Runs
    ) Runs
ON JobModels.JobModelID = Runs.JobModelID 
AND Runs.RowNumber = 1