Shulou(Shulou.com)06/02 Report--

This article introduces why ROW_NUMBER () can not be used in the WHERE clause. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Main reason: the execution order of SQL

Here's a common coding scenario for SQL Server developers:

"I want to see the oldest amount due for each account, along with the account number and due date, ordered by account number."

Since the release of SQL Server 2005, the simplest way to do this has been to use a window function like ROW_NUMBER. In many cases, everything you need can be done in a single SELECT statement with your window function. The trouble comes when you want to incorporate that function in some other way. For instance, using it a WHERE clause.

Let's use this example from AdventureWorks2012:

This works perfectly and gives us every row in the table. Now, let's modify it to meet the scenario we outlined earlier, and only show the oldest order for each account.

Because the WHERE clause already happened.

Logical Query Processing

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:

FROM

WHERE

GROUP BY

HAVING

SELECT

ORDER BY

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.)

Since the WHERE clause happens before the SELECT, it's too late in the process to add the window function to the WHERE clause. It'd have to loop back around a second time to re-evaluate WHERE.

There's No Magic Hack

Unfortunately, the logical query processing model is a fundamental way of doing business for SQL Server, so we can't just cheat around it. Instead, we have to reference our window function through a subquery or CTE (common table expression). In other words, we have to code in a way that makes SQL Server evaluate a WHERE clause after the SELECT containing the window function:

By putting the WHERE clause in the outer query and the window function in the subquery (also called an inner query), we get the window function to come before the WHERE.

As to why you can't use ROW_NUMBER () in the WHERE clause, that's all. I hope the above can help you and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.