Home > Database > Mysql Tutorial > Why Can't I Use ROW_NUMBER() in a WHERE Clause?

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

Linda Hamilton
Release: 2024-12-16 18:37:11
Original
743 people have browsed it

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
Copy after login

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
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template