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?
... View more