Can you filter on a window function result directly in WHERE?

SQL window functions · Experienced

Model answer

No, because window functions are evaluated after WHERE. Put the query in a subquery or CTE and filter in the outer query, for example WHERE rn = 1. Some databases offer QUALIFY, but it is not standard everywhere.

Learn this topic

Window Functions: ROW_NUMBER, RANK and Running Totals in the course SQL for Beginners: Learn Databases Step by Step.

Sign in to add your own answer.

More questions

• What is a window function and how is it different from GROUP BY?
• What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
• What do PARTITION BY and ORDER BY do inside OVER?
• What do LAG and LEAD do?
• How do you calculate a running total?
• How do you find consecutive days (gaps and islands)?