Home Finding the city with the max population
Reply: 1

Finding the city with the max population

Anil Bakirci
1#
Anil Bakirci Published in 2018-02-11 20:40:08Z

I have a test table which looks like:

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?

kbball
2#
kbball Reply to 2018-02-11 20:44:13Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.374997 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO