Selecting the maximum count from a GROUP BY operation

Question

Forgive my SQL knowledge, but I have a Person table with following data -

Id          Name
----        ------
1           a
2           b
3           b
4           c

and I want the following result -

Name      Total
------    ------
b         2

If I use the GROUP BY query -

SELECT Name, Total=COUNT(*) FROM Person GROUP BY Name  

It gives me -

Name   Total
------ ------
a      1
b      2
c      1

But I want only the one with maximum count. How do I get that?

Answer 1

If you want ties

SELECT top (1) with ties Name, COUNT(*) AS [count]
  FROM Person 
 GROUP BY Name  
 ORDER BY count(*) DESC

Answer 2

The easiest way to do this in SQL Server would be to use the top syntax:

SELECT   TOP 1 Name, COUNT(*) AS Total 
FROM     Person 
GROUP BY Name  
ORDER BY 2 DESC