Why Can't I Use ROW_NUMBER() in a WHERE Clause?

Error Encountered while Utilizing ROW_NUMBER() Function in WHERE Clause

A question arose regarding the usage of the ROW_NUMBER() function within the WHERE clause. One user encountered an error stating, "Windowed functions can only appear in the SELECT or ORDER BY clauses" while attempting the following query:

SELECT employee_id
FROM v_employee
WHERE ROW_NUMBER() OVER (ORDER BY employee_id) > 0
ORDER BY employee_id

Solution: Wrapper CTE with Windowed Function

To resolve this error, a common technique involves creating a Common Table Expression (CTE) that encapsulates the windowed function calculation. By wrapping the original query in a CTE, the subsequent query can access the windowed function's results and utilize them in the WHERE clause.

A revised query implementing this solution would appear as follows:

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

By employing this method, the windowed function's output becomes available for use in the WHERE clause, allowing the user to successfully filter the results based on the desired criteria.

The above is the detailed content of Why Can't I Use ROW_NUMBER() in a WHERE Clause?. For more information, please follow other related articles on the PHP Chinese website!