How to use an alias in Hive?

I am trying to find unique cities using the window function, I am not able to use an alias in this query

select branch,city,row_number() over(partition by city order by branch) as row_number from 
sales_report where row_number=1;

Error in query: cannot resolve '`row_number`' given input columns:

Answer

You cannot have a window function in the where clause. Put it in a subquery and do the filter afterwards:

select * from
(
select branch,city,row_number() over(partition by city order by branch) as rn 
from sales_report 
) as t 
where rn = 1;