mysql - MYSQL - How to match the country with the highest deaths for a unique list of continents

Question

I am trying to find the country with the highest number of deaths in each continent. So far, I have the following code which gives me the continent and the highest number of deaths, but I am unable to get the country, as shown in the image 'Code without Country'

SELECT         
    continent,
    MAX(total_deaths) as AllDeaths
FROM covid.covid_
WHERE continent IS NOT NULL
GROUP BY continent
ORDER BY 2 DESC;

Code without Country

However, if I add country to my code, I no longer have what I need

SELECT
    continent,
    country,
    MAX(total_deaths) as AllDeaths
FROM covid.covid_
WHERE continent IS NOT NULL
GROUP BY continent, country
ORDER BY 3 DESC;

Code with Country included

As you can see in the 2nd image, now I have multiple results for each continent, which is not what I need - I need a unique list of all continents, along with the country contributing to the highest deaths in each continent

标签： mysqlsqldatabase

Answer 1

    select b.continent, b.country, a.total_deaths
    from (
        SELECT         
        continent,
        MAX(total_deaths) as AllDeaths
        FROM covid.covid_
        WHERE continent IS NOT NULL
        GROUP BY continent) as a 
    inner join covid.covid_ as b on a.total_deaths = b.total_deaths and a.continent = b.continent