31

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?

Community
  • 1
  • 1
HDunn
  • 533
  • 2
  • 13
  • 26

1 Answers1

48

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)

Taryn
  • 242,637
  • 56
  • 362
  • 405