Why Window Functions Can’t Appear In The WHERE Clause

Kevin Feasel

2017-10-03

T-SQL

Doug Lane explains why window functions like ROW_NUMBER() cannot appear in the WHERE clause (or FROM, GROUP BY, or HAVING):

SQL Server doesn’t process parts of a query in the same order they’re written. Rather than start with SELECT the way we read and write it, here’s the order SQL Server progresses through:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
  7. TOP

The first four steps are all about getting the source data and reducing the result set down. Steps 5 & 6 determine which columns are presented and in which order. Step 7 (TOP) is only applied at the end because you can’t say which rows are in the top n rows until the set has been sorted. (You can read Itzik Ben-Gan’s explanation of this process in way more detail here.)

Definitely worth reading.  Doug also shows how to get around this fact of life and get the equivalent of a window function inside a WHERE clause, at least in terms of function if not necessarily performance.

Related Posts

Generating SQL With Biml

Cathrine Wilhelmsen shows us you can do a lot more with Biml than just generating SSIS packages: This actually happened to me in a previous job. We had a fairly complex ETL solution for the most critical part of our Data Warehouse. Many SSIS packages, views, and stored procedures queried the tables that were replicas […]

Read More

Rowcount Shenanigans When Deleting In Batches

Denis Gobo takes us through a few issues you might run into when deleting data in batches: I have always used WHILE @@rowcount > 0 but you have to be careful because @@rowcount could be 0 when your while loop starts Let’s take a look at an example. This is a simplified example without a where […]

Read More

Categories

October 2017
MTWTFSS
« Sep Nov »
 1
2345678
9101112131415
16171819202122
23242526272829
3031