Invalid column for SELECT with OVER I have the following query:

SELECT 1,
       row_number() over (partition by lower(X), lower(Y) order by X DESC) AS rn
FROM dbo.MyTable
WHERE rn = 1


When I run it, I get a error Invalid column name 'rn'..

I don't understand why I'm getting this as rn is defined in the SELECT portion.

I've tried replacing the WHERE with a HAVING too.
WHERE runs before the window function. So run the whole thing in a derived table.

SELECT t.*
FROM (
  SELECT 1, 
    row_number() over (
      partition by lower(X), lower(Y) order by X DESC
    ) AS rn
  FROM dbo.MyTable
) AS t
WHERE rn = 1;