Invalid Column Using Row_Number()

Question

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?

Answer 1

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;

Answer 2

With your example in the question, you could skip the whole ROW_NUMBER() thing and just use TOP (1):

INSERT INTO @Temp (id, salesID)
SELECT TOP (1) 1, Bama
FROM rusticlines AS rstln  
WHERE Active = '1'
ORDER BY Bama;

For more complex queries (where you need to get first row in a partition), you could probably make something useful with TOP (1) WITH TIES.