Top 5 Distinct Records using Hive

[#1]

I am trying to pull the top five states with the highest measurements related to a specific measureid. My issue is that I am trying to pull DISTINCT states with the highest measurements for each year from 2008 - 2013, i.e., highest state for 2008, 2009, 2010, etc.

My query:

select distinct statename, measureid, reportyear, value  from air_quality  where measureid = 87  and reportyear >= 2008  and reportyear <= 2013  sort by value desc limit 5 

I expect the output with DISTINCT statenames, i.e., I don't want one repeated. If California has the highest for one year, it will not be repeated again.

Currently it displays as "... California, 22 ; ... California, 22 ; ... California, 19 ; ... Arizona, 18 ; ... California, 18"

What are some thoughts?

[#2]

you can try it.

select * from (

select *,row_number() over(partition by statename, measureid, reportyear, value order by value desc ) as num

from air_quality ) as t where t.num=1 and measureid = 87 and reportyear >= 2008 and reportyear <= 2013

Is that right？

[#3]

@bin liu This almost works. It displays every row in the table when I only need the top five distinct states.

[#4]

with t as (select *,row_number() over(partition by statename order by value desc ) as num from air_quality where measureid = 87 and reportyear >= 2008 and reportyear <= 2013)
select statename, measureid, reportyear, value from t where num=1 order by value desc limit 5