Left join using temporary table is not working

Question

I am trying to execute a "left join" on a table "x" but i need the "row_number", so i am using a temporary table with the "with" command but the query does not work and i receive the following error:

SQL Error [156] [S1000]: Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near ')'.`

someone knows how fix this

SELECT telefoneEnriquecido1. FROM VPOTPRP P
    LEFT JOIN (
        WITH tel AS (
            SELECT
                ID_PROSP,
                DT_INC,
                ID_NUM_TEL AS novoTelefone,
                DT_ATC AS dataAtualizacao,
                ROW_NUMBER() OVER (PARTITION BY ID_PROSP ORDER BY DT_ATC DESC) AS rowNumber
            FROM
            VPOTTEL
        )
        SELECT
        FROM tel
        WHERE
        rowNumber = 1
    ) telefoneEnriquecido1 on p.ID_PROSP = telefoneEnriquecido1.ID_PROSP

Answer 1

Strange syntax. I always find CTEs embedded within a query to be confusing, although some databases support them. I don't find that the CTE adds anything to the query; just use a subquery:

SELECT . . .   -- whatever columns you want
FROM VPOTPRP P LEFT JOIN (
     (SELECT ID_PROSP, DT_INC, ID_NUM_TEL AS novoTelefone,
             DT_ATC AS dataAtualizacao,
             ROW_NUMBER() OVER (PARTITION BY ID_PROSP ORDER BY DT_ATC DESC) AS rowNumber
      FROM VPOTTEL
     ) PL
     ON P.ID_PROSP = PL.ID_PROSP AND rowNumber = 1