select state, county, poptotal, rank
from
(select state, county, poptotal, Rank()
          over (Partition BY county
                ORDER BY poptotal DESC)
                as rank
                from midwest
                group by state
                ) as rank
                where rank <= 5