sql server - SQL Row_Number() function in Where Clause

Question

I found one question answered with the Row_Number() function in the where clause. When I tried one query, I was getting the following error:

"Msg 4108, Level 15, State 1, Line 1 Windowed functions can only appear in the SELECT or ORDER BY clauses."

Here is the query I tried. If somebody knows how to solve this, please let me know.

SELECT employee_id 
FROM V_EMPLOYEE 
WHERE row_number() OVER ( ORDER BY employee_id ) > 0 
ORDER BY Employee_ID

Answer 1

To get around this issue, wrap your select statement in a CTE, and then you can query against the CTE and use the windowed function's results in the where clause.

WITH MyCte AS 
(
    select   employee_id,
             RowNum = row_number() OVER ( order by employee_id )
    from     V_EMPLOYEE 
    ORDER BY Employee_ID
)
SELECT  employee_id
FROM    MyCte
WHERE   RowNum > 0