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

Simple Query Zen

Erik Darling wants you to simplify your life queries: See, when a query is big and complicated to you, there’s a pretty good chance you’re gonna get a big and complicated query plan, because it’s big and complicated to the optimizer, too. This isn’t to say the optimizer is dumb or bad or ugly; it’s […]

Read More

Finding Broken Code in SQL Server

Pamela Mooney shows us how we can find broken code on our SQL Server instances: Before we approached our last major SQL Server upgrade, I was curious about what might break.  Yes, I had used the DEA to check our code against deprecated or discontinued code.  But I am talking about code that might not […]

Read More

Categories

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