Why no windowed functions in where clauses?
Title says it all, why can't I use a windowed function in a where clause in SQL Server?
This query makes perfect sense:
select id, sales_person_id, product_type, product_id, sale_amount from Sales_Log where 1 = row_number() over(partition by sales_person_id, product_type, product_id order by sale_amount desc)
But it doesn't work. Is there a better way than a CTE/Subquery?
For what its worth this is the query with a CTE:
with Best_Sales as ( select id, sales_person_id, product_type, product_id, sale_amount, row_number() over (partition by sales_person_id, product_type, product_id order by sales_amount desc) rank from Sales_log ) select id, sales_person_id, product_type, product_id, sale_amount from Best_Sales where rank = 1
+1 for the answers showing with a subquery, but really I'm looking for the reasoning behind not being able to use windowing functions in where clauses.
why can't I use a windowed function in a where clause in SQL Server?
One answer, though not particularly informative, is because the spec says that you can't.
See the article by Itzik Ben Gan - Logical Query Processing: What It Is And What It Means to You and in particular the image here. Window functions are evaluated at the time of the
SELECT on the result set remaining after all the
HAVING clauses have been dealt with (step 5.1).
really I'm looking for the reasoning behind not being able to use windowing functions in where clauses.
The reason that they are not allowed in the
WHERE clause is that it would create ambiguity. Stealing Itzik Ben Gan's example from High-Performance T-SQL Using Window Functions (p.25)
Suppose your table was
CREATE TABLE T1 ( col1 CHAR(1) PRIMARY KEY ) INSERT INTO T1 VALUES('A'),('B'),('C'),('D'),('E'),('F')
And your query
SELECT col1 FROM T1 WHERE ROW_NUMBER() OVER (ORDER BY col1) <= 3 AND col1 > 'B'
What would be the right result? Would you expect that the
col1 > 'B' predicate ran before or after the row numbering?
Why Window Functions Are Not Allowed in WHERE Clauses , With no index to help us avoid Sorting, we gotta do some of that, and we need a Filter operator for our WHERE clause. Since the results of the In my opinion the best way of getting latest budget for each period would be to use the MAX windowed function in the where clause, but this isn't allowed. SELECT b.ACCOUNT_CODE, b.SECONDARY_CODE,
There is no need for CTE, just use the windowing function in a subquery:
select id, sales_person_id, product_type, product_id, sale_amount from ( select id, sales_person_id, product_type, product_id, sale_amount, row_number() over(partition by sales_person_id, product_type, product_id order by sale_amount desc) rn from Sales_Log ) sl where rn = 1
Edit, moving my comment to the answer.
Windowing functions are not performed until the data is actually selected which is after the
WHERE clause. So if you try to use a
row_number in a
WHERE clause the value is not yet assigned.
Of Windowing Functions And Where Clauses, But unlike regular aggregate functions, use of a window function does not The OVER clause determines exactly how the rows of the query are split up for Windowed functions can only appear in the SELECT or ORDER BY clauses (2) Can anyone explain why can't we use windowed functions in group by clause and why it's allowed only in SELECT and ORDER BY I was trying to group the records based on row_number() and a column in SQL Server as like this:
First of all it something called
"All-at-Once Operations" means that all expressions in the same logical query process phase are evaluated logically at the same time.
And great chapter Impact on Window Functions:
Suppose you have:
CREATE TABLE #Test ( Id INT) ; INSERT INTO #Test VALUES ( 1001 ), ( 1002 ) ; SELECT Id FROM #Test WHERE Id = 1002 AND ROW_NUMBER() OVER(ORDER BY Id) = 1;
All-at-Once operations tell us these two conditions evaluated logically at the same point of time. Therefore, SQL Server can evaluate conditions in WHERE clause in arbitrary order, based on estimated execution plan. So the main question here is which condition evaluates first.
If ( Id = 1002 ) is first, then if ( ROW_NUMBER() OVER(ORDER BY Id) = 1 )
If ( ROW_NUMBER() OVER(ORDER BY Id) = 1 ), then check if ( Id = 1002 )
So we have a paradox.
This example shows why we cannot use Window Functions in WHERE clause. You can think more about this and find why Window Functions are allowed to be used just in SELECT and ORDER BY clauses!
Filters results of a previously computed ordered analytical function according to user‑specified search conditions.
SELECT Id FROM #Test WHERE Id = 1002 QUALIFY ROW_NUMBER() OVER(ORDER BY Id) = 1;
QUALIFY does with window functions what HAVING does with aggregate functions and GROUP BY clauses.
In the execution order of a query, QUALIFY is therefore evaluated after window functions are computed. Typically, a SELECT statement’s clauses are evaluated in the order shown below:
FromWhere Group by Having Window QUALIFY Distinct Order by Limit
Documentation: 9.1: Window Functions, But unlike regular aggregate functions, use of a window function does not cause the ORDER BY clause would, except that it treats every partition as separate. SQL Server offers many handy functions that can be used either in your SELECT clause or in your WHERE clause. For the most part these functions provide complex coding that would be very difficult to get this same functionality without these functions.
You don't necessarily need to use a CTE, you can query the result set after using row_number()
select row, id, sales_person_id, product_type, product_id, sale_amount from ( select row_number() over(partition by sales_person_id, product_type, product_id order by sale_amount desc) AS row, id, sales_person_id, product_type, product_id, sale_amount from Sales_Log ) a where row = 1
SQL Window Functions, In my opinion the best way of getting latest budget for each period would be to use the MAX windowed function in the where clause, but this isn't I found one question answered with the Row_Number() function in the where clause. When I tried one query, I was getting the following error: "Msg 4108, Level 15, State 1, Line 1 Windowed functions can only appear in the SELECT or ORDER BY clauses." Here is the query I tried. If somebody knows how to solve this, please let me know.
Finally, there's the old-fashioned, pre-SQL Server 2005 way, with a correlated subquery:
select * from Sales_Log sl where sl.id = ( Select Top 1 id from Sales_Log sl2 where sales_person_id = sl.sales_person_id and product_type = sl.product_type and product_id = sl.product_id order by sale_amount desc )
I give you this for completeness, merely.
Windowed Function Where Clause – SQLServerCentral, If there is no PARTITION BY clause, then the entire result set of the query is a single partition. Window-function processing is Window functions aren't permitted in UPDATE statements because UPDATE isn't compatible with SELECT or ORDER BY. Window functions are like scoped SELECT statements that re-examine the relevant rows and apply conditions like PARTITION BY and ORDER BY.
Window Functions, Users who are not familiar with window functions, rank-related functions, Although the ORDER BY clause is optional for some window functions, it is required The result after the SELECT step 5.1. with any window function is still relational. Also, speaking strictly, the reason why window function are not allowed in the WHERE clause is not because it would create ambiguity, but because the order how Logical Query Processing processes SELECT statement in T-SQL.
Window Functions, A window function then computes a value for each row in the window. This applies only to functions that do not require ORDER BY clause. All database users know about regular aggregate functions which operate on an entire table and are used with a GROUP BY clause. But very few people use Window functions in SQL. These operate on a set of rows and return a single aggregated value for each row.
OVER Clause (Transact-SQL), All of the ranking functions depend on the sort ordering specified by the ORDER BY clause of the associated window definition. Rows that are not SQL Window Function Example. Window functions can be called in the SELECT statement or in the ORDER BY clause. However, they can never be called in the WHERE clause. You’ll notice that all the examples in this article call the window function in the SELECT column list. Let’s go to the first SQL window function example.