SQL Server – Invalid Column Using ROW_NUMBER()

sql serversql-server-2008-r2t-sql

I am attempting to only select the 1st row returned from the query. This is my syntax ->

insert into @Temp (id, salesID)
select
    RN = ROW_NUMBER() OVER(order by t.psUserID)
    ,t.psUserID
from (
    select distinct 
    psUserID = rstln.Bama   
    From rusticlines rstln  
    AND tnr.Active = '1') t
WHERE RN = 1;

And I get the error:

Msg 207, Level 16, State 1, Line 51
Invalid column name 'RN'.

What do I need to change so that it only selects the 1st row number?

Best Answer

There is a Logical Processing Order of the SELECT statement. Anything can reference an item further up the list than itself but nothing at its own level or below.

RN doesn't exist yet in your query. You can use a derived table or a CTE to fix the error.

insert into @Temp (id, salesID)
select RN, psUserID
from (
select
    RN = ROW_NUMBER() OVER(order by t.psUserID)
    ,t.psUserID
from (
    select distinct 
    psUserID = rstln.Bama   
    From rusticlines rstln       -- more syntax issues 
    AND tnr.Active = '1'         -- here
     ) t) d
WHERE RN = 1;
Related Question