"Incorrect syntax" when using a common table expression

Question

WITH list_dedup (Company, duplicate_count) AS
(
     SELECT
         *,
         ROW_NUMBER() OVER (PARTITION BY Company ORDER BY Email) AS 'RowNumber'
     FROM
         Travels
)

Error:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.

Answer 1

You are missing a final select for the common table expression (after the definition of the CTE):

WITH list_dedup  (Company,duplicate_count) As
(
  select *,
         ROW_NUMBER() OVER (PARTITION BY Company ORDER by Email) As "RowNumber"
  From Travels
)
select *  
from list_dedup;

But this will not because the CTE is defined to have two columns (through the WITH list_dedup (Company,duplicate_count)) but your select inside the CTE returns at least three columns (company, email, rownumber). You need to either adjust the column definition for the CTE, or leave it out completely:

WITH list_dedup As
(
  select *,
         ROW_NUMBER() OVER (PARTITION BY Company ORDER by Email) As "RowNumber"
  From Travels
)
select *  
from list_dedup;

---

The As "RowNumber" in the inner select also doesn't make sense when the column list is defined, because then the CTE definition defines the column names. Any alias used inside the CTE will not be visible outside of it (if the CTE columns are specified in the with .. (...) as part).

Answer 2

You've just set up your CTE - now you need to use it!

WITH list_dedup (Company, duplicate_count) AS
(
     SELECT
         *,
         ROW_NUMBER() OVER (PARTITION BY Company ORDER BY Email) AS 'RowNumber'
     FROM
         Travels
)
SELECT *
FROM list_dedup