CODEWITHSUNDEEP
sqlapache-sparkhive
ssk
ssk

Reputation: 55

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:

Upvotes: 0

Views: 627

Answers (1)

mck
mck

Reputation: 42332

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;

Upvotes: 2

Related Questions