Sql using With Clause

Question

Hi

I am writing below using With clause but i am getting error "Incorrect syntax near RNum"

WITH CTE AS
(SELECT *,DENSE_RANK()OVER(ORDER BY L_key DESC)) as RNum
FROM DBO.Rep_Def_TMP

Please suggest.

Thanks

Answer 1

Your SQL statement is wrong. A WITH clause must start with a semicolon. And a WITH is a kind on inline view, which you use/query later in the SQL statement like here SELECT * FROM CTE:

;WITH CTE AS  
   (SELECT *, DENSE_RANK() OVER(ORDER BY L_key DESC) as RNum  
    FROM DBO.Rep_Def_TMP)  
SELECT *  
FROM CTE