Find the MAX value of an Aggregate Query In SQL Server 2008: SELECT the U.S. State with the Most Cities

Ok expert SQL Devs, I hope this is 开发者_如何学JAVAas simple as it sounds. Basically I want to form a query to find the number of cities per state AND only contain the State with the most cities in the result set.

Something like:

SELECT S.Name, MAX(COUNT(C.StateID) as 'Count') --NO Can do, please help!
FROM tblStates as S JOIN tblCities as C
ON S.StateID = C.StateID
GROUP BY S.Name

Please let me know the easiest way to make this Query work.

Thanks

SELECT TOP 1
    S.Name, COUNT(C.StateID) as CityCount
FROM
    tblStates as S
INNER JOIN
    tblCities as C
ON
    S.StateID = C.StateID
GROUP BY
    S.Name
ORDER BY
    COUNT(C.StateID) DESC

There might be a more elegant way but this should work:

SELECT s.Name, MAX(Count) FROM
    (SELECT S.Name, COUNT(C.StateID) as 'Count' --NO Can do, please help!
    FROM tblStates as S JOIN tblCities as C
    ON S.StateID = C.StateID
    GROUP BY S.Name)

SELECT S.Name, COUNT(C.StateID) AS 'Count' 
FROM tblStates as S JOIN tblCities as C 
ON S.StateID = C.StateID 
GROUP BY S.Name 
ORDER BY COUNT(C.StateID) DESC LIMIT 1;

Some dialects (e.g. Postgres) allow a column number in the ORDER clause: ORDER BY 2 DESC.