CODEWITHSUNDEEP
mysqlsqlmysql-workbench
nivek
nivek

Reputation: 23

Finding Max of Max mysql

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.

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...

Upvotes: 2

Views: 63

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522181

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;

Upvotes: 3

Related Questions