Finding Max of Max mysql-CodePudding

I am using a table called covid_vaccinations.

To briefly explain about the table, it tracks down all the countries' vaccination completion by every single day from Feb xx, 2020 to Jan XX, 2022.

The name of the countries are called 'location' in this table.
The countries (location) are also categorized in the column of 'continent'

To find the people who are fully vaccinated in Asia, I used the query below:

SELECT continent,location, MAX(people_fully_vaccinated)
FROM covid_vaccinations
WHERE continent LIKE '%ASIA%'
GROUP BY continent, location
ORDER BY 3 DESC;

I used MAX() since the <people_fully_vaccinated> column includes the cumulative number of data.

The query above gave me the result I wanted, see <image 1>

HERE IS MY QUESTION:

If I just want to get the GREATEST result of people_fully_vaccinated, how should I write the query? I tried below, and it gave me the same result as <image 1>

SELECT location, MAX(peep_f_vacc_asia)
FROM (
     SELECT location, MAX(people_fully_vaccinated) as peep_f_vacc_asia
     FROM covid_vaccinations
     WHERE continent LIKE '%ASIA%'
     GROUP BY continent,location
     ) A
GROUP BY location
ORDER BY 2 DESC;

The desired result I want to see would be only a single row, China (which has the greatest number of people_fully_vaccinated)

Thank you so much guys...

CodePudding user response：

You might be able to get away with just using a LIMIT query. A slight modification of your first query:

SELECT continent, location, MAX(people_fully_vaccinated)
FROM covid_vaccinations
WHERE continent LIKE '%ASIA%'
GROUP BY continent, location
ORDER BY 3 DESC
LIMIT 1;

But this only works in the case that there are no ties for a given continent and location for the max number of fully vaccinated. If you do have to worry about ties, and you are using MySQL 8 , then we can use RANK as follows:

WITH cte AS (
    SELECT continent, location, MAX(people_fully_vaccinated) AS max_fv,
           RANK() OVER (ORDER BY MAX(people_fully_vaccinated) DESC) rnk
    FROM covid_vaccinations
    WHERE continent LIKE '%ASIA%'
    GROUP BY continent, location
)

SELECT continent, location, max_fv
FROM cte
WHERE rnk = 1;