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