How do I return only one row with MAX() aggregate?-CodePudding

SELECT Name, MAX(Population) as Population

FROM County

GROUP BY Name;

Question: This just returned 16 rows... I want to return only the ONE county which has the highest population, showing 1 row with with the name of that county and its population,, not the highest population for every county. How do I fix this? Thanks in advance.

CodePudding user response：

Order and take first

SELECT Name, 
       Population 
FROM County 
ORDER BY Population DESC LIMIT 1

CodePudding user response：

with data (Population, Name) as(
    Select '12345'        ,'c1'  union all
    Select '1234500001'   ,'c2'  union all
    Select '1234500002'   ,'c3'  union all
    Select '12346'        ,'c4'  union all
    Select '1234600001'   ,'c4'  union all
    Select '1234600002'   ,'c4' )
SELECT Name, Population
FROM data
where  Population = (SELECT MAX(Population) FROM data)
;

CodePudding user response：

Order by MAX in Descending and take Top 1

SELECT TOP 1 Name, MAX(Population) as Population FROM County
GROUP BY Name
ORDER BY MAX(Population) desc;