Finding Max of Max mysql

Solution 1:

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;