Using ranking-function derived column in where clause (mssql08)

Question

Hoping this is trivial for a SQL-Ninja... Been trying to get the following query working:

This is under MSSQL Server 2008

SELECT ROW_NUMBER() OVER (ORDER BY Date_Time DESC) AS RowNumber, *
FROM
( SELECT T.A_ID, T.User_Name, T.Date_Time, T.Value,
         U.ID, U.Name, U.Field1, U.Field2,
         COUNT(U.ID) OVER () AS TotalRows
  FROM 
    TeeTable as T INNER JOIN YouTable AS U
    ON T.U_ID = U.ID
    WHERE T.Value BETWEEN 222 AND 225
) Filtered

WHERE RowNumber BETWEEN 1 AND 5

The values are somewhat contrived to give a specific example, but the spirit of the query is completely preserved. The error I get from this statement is:

Invalid column name 'RowNumber'.

If I remove the final WHERE clause (RowNumber BETWEEN ...) it returns an expected result set (A_ID, User_Name, Date_Time etc...), with RowNumber as a column (with sensical values) in said results. Yet I cannot compare against it in the WHERE clause. I'm clearly doing something stupid but this hits my SQL limit!

I've tried re-arranging this as a CTE as well, (WITH Filtered AS ...) but the end result is the same, it appears that's just a sugar for what I'm already doing anyway.

Ideas? How can I filter against the RowNumber derived column?

Answer 1

You must move the WHERE operator above the project list where RowNumber column is created. Use a derived table or a CTE:

SELECT * 
  FROM (
   SELECT *, ROW_NUMBER() OVER (...) as RowNumber
   FROM ...) As ...
 WHERE RowNumber = ...

the equivalent CTE is:

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (...) as RowNumber
       FROM ...)
SELECT * FROM cte 
WHERE RowNumber = ...

Answer 2

The Window Functions (of which ROW_NUMBER is the best know) are filled in very late in the query, well after the WHERE clause. Therefore you have to nest it too, in order to filter on it:

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Date_Time DESC) AS RowNumber, *
    FROM
    ( SELECT T.A_ID, T.User_Name, T.Date_Time, T.Value,
             U.ID, U.Name, U.Field1, U.Field2,
             COUNT(U.ID) OVER () AS TotalRows
      FROM 
        TeeTable as T INNER JOIN YouTable AS U
        ON T.U_ID = U.ID
        WHERE T.Value BETWEEN 222 AND 225
    ) Numbered
)  Filtered
WHERE RowNumber BETWEEN 1 AND 5

You can also put them in CTE's or Views to get the same effect.