Google Spreadsheets: How to query countries with max population by continent?

Let's say I have a table with the columns country, continent and population.

How can I use the QUERY function in Google Spreadsheets to select, for each continent, only the country with the highest population?

In regular SQL I think I'd use HAVING, but this doesn't seem to be an option here.

3 Answers
3

=SORTN(SORT(G:J,4,0),2^99,2,3,0)


SORTN

I suggest a helper column, say K with:

=if(maxifs(J:J,I:I,I2)=J2,"#","")


in K2 and copied down to suit, then:

=query(G:K,"select I,H,J where K is not NULL")


A couple of suggestions (both a bit long) where I've got Rank, Country and Continent in columns A, B and C (sorry it would have taken me too long to type in the populations)

To get a list in descending order of rank:

=ArrayFormula({unique(filter(C:C,C:C<>"")),
vlookup(Query(A:C,"select min(A) where A is not null group by C order by min(A) label min(A) 'Rank'"),A:C,2,false)})


To get a list in alphabetical order of continent:

=ArrayFormula({Query(A:C,"select C,min(A) where A is not null group by C label min(A) 'Rank'"),
vlookup(Query(A:C,"select min(A) where A is not null group by C label min(A) 'Rank'"),A:C,2,false)})


Although the list (I imagine) would go all the way down to Vatican City (pop about 1,000) most countries have at least several thousand inhabitants so I guess ties are pretty unlikely :-)

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.