0

I've written the following SQL query against MySQL's good old world database:

SELECT Continent, Region, Name, MAX(SurfaceArea) 
FROM world.country 
GROUP BY Continent, Region;

The country table looks something like this:

| Code  | Name          | Continent | Region                      | SurfaceArea | ...
| 'AFG' | 'Afghanistan' | 'Asia'    | 'Southern and Central Asia' | 652090.00   |
| 'ALB' | 'Albania'     | 'Europe'  | 'Southern Europe'           |'28748.00'   |
...

My query above works in MySQL InnoDb v5.7.26 and emits the correct MAX(SurfaceArea) per Continent and Region. However, the Name emitted is incorrect, in that it is not the name of the country with the most surface area.

For example, note the United Arab Emirates row here:

| Continent | Region         | Name                   | MAX(SurfaceArea) |
| 'Asia'    | 'Eastern Asia' | 'China'                | '9572900.00'     |
| 'Asia'    | 'Middle East'  | 'United Arab Emirates' | '2149690.00'     |
...

This is because selecting a non-aggregate, non-literal column that is not part of the GROUP BY clause does not make much sense. I believe MySQL just arbitrarily defaults to the first value it encounters in the group.

My question is - How can I write the above query correctly, preferably without using a JOIN?

That is, return a table with the Name and the SurfaceArea of the country with the MAX(SurfaceArea) per each Continent and Region.

urig
  • 16,016
  • 26
  • 115
  • 184
  • Thank you @Shadow for linking to an almost identical question with answers. This answer is pretty much what I was looking for: https://stackoverflow.com/a/7745679/33404 – urig May 20 '19 at 20:22
  • 1
    it's great to see that someone finally appreciates a duplicate closure as help and does not take it as a personal insult! – Shadow May 20 '19 at 20:51

0 Answers0