SQL telling me a column I created is not found

Question

I'm sure I'm missing something in the syntax, but I'm just not seeing it. If I just created the column rn why is it an invalid column?

select 
	   [PlanID]
      ,[SubSystemId]
       ,[Cat3]
	  ,[factor]
	  ,[ValidFrom]
      ,row_number() 
	  
	  over(partition by 	  
	  [PlanID]
      ,[SubSystemId]
      ,[Cat3]
	  
	  order by [ValidFrom] desc) as rn

	  from DB.[dbo].[sometable] as T
	  where
rn = 1      

Msg 207, Level 16, State 1, Line 20
Invalid column name 'rn'.

Answer 1

A SELECT query is logically evaluated in this order: FROM-JOIN-WHERE-GROUP BY-HAVING-SELECT-ORDER BY. Thus, something you define in the SELECT list cannot be used in the WHERE clause. (But it can be used in ORDER BY).

The way to resolve this issue is to use nested queries. This is often done with Common Table Expressions (CTE). For instance:

; WITH numbering AS (
   select [PlanID]
         ,[SubSystemId]
          ,[Cat3]
	     ,[factor]
	     ,[ValidFrom]
         ,row_number()  over(partition by [PlanID], [SubSystemId] ,[Cat3]
                             order by [ValidFrom] desc) as rn
	from DB.[dbo].[sometable] as T
)
SELECT * 
FROM   numbering 
WHERE  rn = 1

Answer 2

rn is a column alias in the result set and not valid in the where clause.

you can use a with:

with T as (
  select 
	   [PlanID]
      ,[SubSystemId]
       ,[Cat3]
	  ,[factor]
	  ,[ValidFrom]
      ,row_number() over(partition by 	  
	        [PlanID]
           ,[SubSystemId]
           ,[Cat3]
	       order by [ValidFrom] desc) as rn
   from DB.[dbo].[sometable]
) 

select *
from T
where rn = 1;      

or use a derived table:

select *
from (select 
	   [PlanID]
      ,[SubSystemId]
       ,[Cat3]
	  ,[factor]
	  ,[ValidFrom]
      ,row_number() 	  
	  over(partition by 	  
   	     [PlanID]
        ,[SubSystemId]
        ,[Cat3]
	    order by [ValidFrom] desc) as rn
   from DB.[dbo].[sometable]
) as T
where rn = 1;    

Answer 3

Hi @Dom

Msg 207, Level 16, State 1, Line 20 Invalid column name 'rn'.

As experts answered above, it is caused by the logical processing steps. The alias you specified in Select (Step5) cannot be used in Where (Step 2).

You could also find workarounds in above answers.

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)