How to Use ROW_NUMBER() in a WHERE Clause?

Querying Using ROW_NUMBER() Function in WHERE Clause

In an attempt to utilize the ROW_NUMBER() function in a WHERE clause, a query encountered the error "Windowed functions can only appear in the SELECT or ORDER BY clauses." The query in question was:

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

To resolve this error, employ a workaround by wrapping the select statement within a Common Table Expression (CTE). This allows querying against the CTE and utilizing 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

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