Row_Num Partition in WHERE clause

Question

I've successfully added a ROW_NUMBER() function to my SELECT statement, but I get an "invalid column name" error when I try to reference the column I generated with the ROW_NUMBER() function in my WHERE clause by referencing the [RANK] alias.

Query

SELECT DISTINCT 
      ROW_NUMBER () OVER (PARTITION BY T0.DocNum ORDER BY T0.DocNum) [RANK],      
      T0.DocNum,
      T3.[PickIdNo], 
      T0.[NumAtCard],
      T0.[U_STS_SalesProg],
      T0.[DocDate], 
      T0.[DocDueDate], 
      T0.[TaxDate],
      T0.[CardCode], 
      T0.[CardName], 
      T6.[SlpName], 
      T8.[firstName][CSS], 
      T4.[U_BOTYPE],
      T1.LineTotal BackOrderTotal,
      T2. OrderTotal
FROM ORDR T0
INNER JOIN
(SELECT
      T0.DocEntry
      , ROUND(SUM(T0.[OpenQty]*T0.[Price]),2) LineTotal
FROM RDR1 T0 where  LineStatus ='O'
GROUP BY T0.DocEntry) T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN
(SELECT
      T0.DocEntry
      , ROUND(SUM(T0.[Quantity]*T0.[Price]),2) OrderTotal
FROM RDR1 T0
GROUP BY T0.DocEntry) T2 ON T0.DocEntry = T2.DocEntry

INNER JOIN RDR1 T3 ON T0.[DocEntry] = T3.[DocEntry] 
INNER JOIN OCRD T4 ON T0.[CardCode] = T4.[CardCode] 
INNER JOIN OTER T5 ON T4.[Territory] = T5.[territryID] 
INNER JOIN OSLP T6 ON T4.[SlpCode] = T6.[SlpCode] 
INNER JOIN OITM T7 ON T3.[ItemCode] = T7.[ItemCode] 
INNER JOIN OHEM T8 ON T4.[DfTcnician] = T8.[empID] 
INNER JOIN OITW T9 ON T7.[ItemCode] = T9.[ItemCode]

WHERE Rank = '1' -- <<== This is where it fails
ORDER BY T8.[firstName], T0.[CardName], t0.DocNum

Answer 1

From Itzik Ben-Gan's Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Developer Reference):

Logical Query Processing Order:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
   1. Evaluate Expressions
   2. Remove Duplicates
6. ORDER BY
7. OFFSET-FETCH/TOP

Window Functions operate in steps 5.1. and 6.

Obviously then, you cannot access the results of a Window Function in the WHERE or HAVING clauses. You can always use a CTE or a subquery.

<sub>See also:
- Logical Processing Order of the SELECT statement (SQL Server 2014 - 2017)
- Logical Query Processing: What It Is And What It Means to You (Itzik Ben Gavin - ITProToday)</sub>