I have a table with data about cities that includes their name, population and other fields irrelevant to my question.
ID Name Population
1 A 45667
2 B 123456
3 C 3005
4 D 13769
To find the max population is basic, but I need a resulting table that has the max population in one column, and the corresponding city's name in another column
Population Name
123456 B
I've looked through similar questions, but for some reason the answers look over-complicated. Is there a way to write the query in 1 or 2 lines?
There are several ways that this can be done:
A filter in the WHERE
clause:
select id, name, population
from yourtable
where population in (select max(population)
from yourtable)
Or a subquery:
select id, name, population
from yourtable t1
inner join
(
select max(population) MaxPop
from yourtable
) t2
on t1.population = t2.maxpop;
Or you can use TOP WITH TIES
. If there can be no ties, then you can remove the with ties
. This will include any rows that have the same population value:
select top 1 with ties id, name, population
from yourtable
order by population desc
Since you are using SQL Server you can also use ranking functions to get the result:
select id, name, population
from
(
select id, name, population,
row_number() over(order by population desc) rn
from yourtable
) src
where rn = 1
See SQL Fiddle with Demo of all.
As a side note on the ranking function, you might want to use dense_rank()
instead of row_number()
. Then in the event you have more than one city with the same population you will get both city names. (See Demo)