CODEWITHSUNDEEP
sql-servert-sql
A.B.
A.B.

Reputation: 2470

Finding the city with the max population

I have a test table which looks like:
enter image description here

And I want to find the city with the max population. Here is my query:

SELECT City, count(*) AS [Population]
INTO #stats
FROM [Customer]
GROUP BY City

SELECT City, [Population]
FROM #stats
WHERE [Population] = 
(
    SELECT max([Population])
    FROM #stats
)

DROP TABLE #stats

Is it possible to do it in one query, without using a temp table? What would other shorter practical ways be to get that result?

Upvotes: 2

Views: 1191

Answers (1)

kjmerf
kjmerf

Reputation: 4345

Yes. You can use:

SELECT TOP 1
city, COUNT(*) AS population
FROM Customer
GROUP BY city
ORDER BY population DESC

So just use the query you have already written to derive the population by city, but select only the top result and make sure you order by population in descending order.

Upvotes: 5

Related Questions