find maximum count in sql-CodePudding

I have a table containing a sample data of covid variant cases

country      covid_variant       cases
 USA           SARS Covid        2000
 USA           Delta             100
 USA           Omicron           500
 Mexico        SARS Covid        2000
 USA           Omicron           400

How can I get the data based on max cases of each variant?

covid_variant   countries   max_cases
 SARS Covid      USA           2000
 SARS Covid      Mexico        2000
 Delta           USA           100
 Omicron         USA           500

CodePudding user response：

Using ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY covid_variant, country
                                 ORDER BY cases DESC) rn
    FROM yourTable
)

SELECT covid_variant, country, cases
FROM cte
WHERE rn = 1;

CodePudding user response：

Supposing this is MSSQL, with TSQL syntax, one possible answer is:

select covid_variant, country, MAX(cases) as max_cases
from [tablename] 
group by covid_variant, country
order by covid_variant, country

CodePudding user response：

If your using MySQL 5.7 try using MAX and GROUP BY.

SELECT covid_variant, 
       country, 
       MAX(cases) AS max_cases 
FROM your_table 
GROUP BY covid_variant, country 
ORDER BY max_cases DESC, country DESC;

RESULT

covid_variant  country  max_cases  
-------------  -------  -----------
SARS Covid     USA             2000
SARS Covid     Mexico          2000
Omicron        USA              500
Delta          USA              100