Back to articles list March 27, 2020 - 3 minutes read Why Window Functions Are Not Allowed in WHERE Clauses Agnieszka Kozubek-Krycuń Agnieszka is a Chief Content Officer at Vertabelo. Before coming to Vertabelo, she worked as a Java programmer. She has a PhD in mathematics and over 10 years of experience in teaching mathematics and computer science at the University of Warsaw. In her free time, she enjoys reading a good book, going mountain hiking and practicing yoga. Tags: sql learn sql window functions Window functions are extremely powerful, but there are a lot of nuances and little details you need to learn about using window functions in order to use them correctly and efficiently. A fairly common question SQL users have is why window functions are not allowed in WHERE. TL;DR: You can use window functions in SELECT and ORDER BY. You cannot use window functions in WHERE, GROUP BY, or HAVING. Use a subquery or a WITH query to get around this limitation. Read on to find out why. Using ROW_NUMBER() in a WHERE Clause Here is an example. The query below tries to find salespeople who made the sale with the highest amount for each product. It seems like a perfect use for the ROW_NUMBER() function. Let’s try: we want to number the rows within each product, in the order from the highest to the lowest by sale amount. This is a typical attempt for using window functions in WHERE. SELECT id, product_id, salesperson_id, amount FROM sale WHERE 1 = row_number() over(PARTITION BY product_id ORDER BY amount DESC); However, when we run the query, we get an error: ERROR: window functions are not allowed in WHERE LINE 3: WHERE 1 = row_number() over(PARTITION BY product_id ORDER BY amount DESC) Why Can’t I Use ROW_NUMBER() in WHERE? You can’t use window functions in WHERE, because the logical order of operations in an SQL query is completely different from the SQL syntax. The logical order of operations in SQL is: FROM, JOIN WHERE GROUP BY aggregate functions HAVING window functions SELECT DISTINCT UNION/INTERSECT/EXCEPT ORDER BY OFFSET LIMIT/FETCH/TOP This means that the filters in a WHERE clause are processed before window functions. At the time the WHERE clause is processed, the window functions would not have been computed yet. And if they are not yet computed, you can’t refer to them in WHERE. This order of operations implies that you can only use window functions in SELECT and ORDER BY. That is, window functions are not accessible in WHERE, GROUP BY, or HAVING clauses. For this reason, you cannot use any of these functions in WHERE: ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), or NTILE(). Is there a way around it? Fortunately, yes! How Do I Use Window Functions in a WHERE Clause Then? The solution is to use either a subquery or a WITH query (aka Common Table Expression). Take a look at the following: WITH sales_numbered AS ( SELECT id, product_id, salesperson_id, amount, row_number() over(PARTITION BY product_id ORDER BY amount DESC) AS rn FROM sale ) SELECT id, product_id, salesperson_id, amount FROM sales_numbered WHERE rn = 1; In the WITH query, we select the columns from the table sales and compute the row number value for each row. In the main query, we use the computed row number value, rn, to filter the rows with the row number equal to 1. Why Can’t I Use Window Functions in WHERE? You can’t use window functions in WHERE, because they would not have been evaluated yet at the time the WHERE filters are processed. You can use window functions only in SELECT and ORDER BY. If you would like to learn more about window functions in SQL, try our interactive “Window Functions” course. We teach window functions syntax on real world examples, discuss common mistakes, and explain little caveats that will make your work more effective. Tags: sql learn sql window functions You may also like SQL Window Functions Cheat Sheet This 2-page SQL Window Functions Cheat Sheet covers the syntax of window functions and a list of window functions. Download it in PDF or PNG format. Read more SQL Course of the Month – Window Functions Find out why you should learn SQL window functions in April and why you should do it in our course. Read more Common SQL Window Functions: Using Partitions With Ranking Functions Once you’ve learned such window functions as RANK or NTILE, it’s time to master using SQL partitions with ranking functions. Read more How to Use Rank Functions in SQL In this article, you’ll learn how to use rank functions in SQL. It’ll give you a solid foundation for getting deeper into SQL window functions. Read more SQL Window Function Example With Explanations Interested in how SQL window functions work? Scroll down to see our SQL window function example with definitive explanations! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.