1

This question already has an answer here:

When I run the following SQL command

select payee_id, start_dt, 
       row_number() over(partition by payee_id order by start_dt) as rn 
from xxx 
where rn = 1

I got an error :

error Invalid identifier 'rn'

Please advise.

marked as duplicate by Philᵀᴹ, Shanky, Paul White Feb 9 '16 at 18:39

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

4

rn cannot be referenced in WHERE because it's defined in the SELECT clause. Try using a subquery instead:

SELECT *
FROM (
    select payee_id,start_dt,
           row_number() over(partition by payee_id order by start_dt) as rn
    from xxx
) AS sub
where rn = 1;

For more reading, google "logical query processing order".

Not the answer you're looking for? Browse other questions tagged or ask your own question.