CODEWITHSUNDEEP
sql-serveraggregate-functions
Nautic
Nautic

Reputation: 13

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

Upvotes: 1

Views: 3443

Answers (3)

Andrew Lazarus
Andrew Lazarus

Reputation: 19340

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.

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85096

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)

Upvotes: 0

amit_g
amit_g

Reputation: 31260

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

Upvotes: 2

Related Questions