Google Spreadsheets: How to query countries with m

问题:

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.

回答1:

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

• SORT by population in descending order,if not done already
• Remove Duplicates with SORTN

回答2:

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")

回答3:

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 :-)

---

UPDATE - with population data

回答4:

In QUERY method Select Continent, maximum of the population and need to group by Continent:

=QUERY(Countries,"select I, max(J) GROUP BY I",1)

This will return the below results from the table:

Continent       max Population
Africa          173615345
Asia            1385566537
Australia       23342553
Europe          82726626
North America   320050716
South America   200361925