SQL Query on partitioning does not run

Question

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.

Answer 1

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".