Invalid Column Using Row_Number()

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?

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.

StackExchange Database Administrators Q#173367, answer score: 10