Why no windowed functions in where clauses?

by

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 WHERE/JOIN/GROUP BY/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?

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. How do I UPDATE from a SELECT in SQL Server?
  3. Get top 1 row of each group
  4. How to return only the Date from a SQL Server DateTime datatype
  5. CROSS JOIN vs INNER JOIN in SQL
  6. Referring to a Column Alias in a WHERE Clause
  7. Function vs. Stored Procedure in SQL Server
  8. Generate a resultset of incrementing dates in TSQL
  9. SQL Server check case-sensitivity?
  10. How to update Identity Column in SQL Server?
  11. How to assign an exec result to a sql variable?
  12. Concatenate values based on ID
  13. Query error with ambiguous column name in SQL
  14. Splitting delimited values in a SQL column into multiple rows
  15. SQL: parse the first, middle and last name from a fullname field
  16. Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]
  17. varbinary to string on SQL Server
  18. Scope of temporary tables in SQL Server
  19. SQL Server 2005 ROW_NUMBER() without ORDER BY
  20. Can an INNER JOIN offer better performance than EXISTS
  21. Find the real column name of an alias used in a view?
  22. Stored procedure that exports data into csv files only exports to one file
  23. Get Start and End Date from Week Number SQL Server
  24. How to get the identity of an inserted row?
  25. Get the last day of the month in SQL
  26. How to create a pivot query in sql server without aggregate function
  27. SQL take just the numeric values from a varchar
  28. SQL Server FOR XML Path make repeating nodes
  29. Why is RAND() not producing random numbers?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment