For each country, find city that has the highest population and the city's population

Question

I am using the Mondial database schema and am trying to find: For each country, find city that has the highest population and the city's population.

Right now I have:

SELECT Country.Name, city.name, MAX(city.population) Population
FROM city
Join Country
On Country.Code=City.Country
WHERE city.population IS NOT NULL
GROUP BY Country.Name, city.name
ORDER BY Country.Name;

This gives me ALL of the cities in each country and their populations and not just the largest city.

You should provide the table create and insert statements as sample data. At least build smaple schema in SQL Fiddle and link it to the question. We don't have your `city` and `country` tables. Also, you should show the desired output instead of just explaining in words. Do not add [tag:sqlplus] unless it is related to it. Your question is related to Oracle SQL, while SQL*Plus is a client tool. See [**how to ask questions**](http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html) — Lalit Kumar B, Nov 17 '15 at 05:55
Possible duplicate of [must appear in the GROUP BY clause or be used in an aggregate function](http://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function) — Sebas, Nov 17 '15 at 06:10

score 3 · Accepted Answer · answered Nov 17 '15 at 05:32

Use analytical functions. Something like this should work (untested):

select
  country.name,
  city.name,
  city.population
from
  country
join
(
  select
    country,
    name,
    population,
    row_number() over ( partition by population desc) as rn
  from
    city
) city on
  city.country = country.code
  and city.rn = 1
order by
  country.name

score 2 · Answer 2 · answered Nov 17 '15 at 05:50

Don't know in oracle but if done in SQL Server it can be done like this:

    Select * from
        (select 
        Country.Name,
        city.name,
        city.population,
        ROW_NUMBER() over(partition by Country.Name order by Country.Name,city.population desc) RowNum
    from Country inner join city city on Country.Code=City.Country) tbl
    where RowNum = 1

function similar to row_number in oracle will help.
Hope This help.

score 0 · Answer 3 · answered Jan 30 '17 at 21:15

This seems to work.

It's also useful for filtering query results according to column containing an aggregate function.

SELECT ct.name AS "Country", c1.name AS "City", c1.population AS "Population"
FROM   city c1
    JOIN country ct
    ON c1.country = ct.code
WHERE  c1.population = (SELECT max(population)
                        FROM   city c2
                        WHERE  c1.country = c2.country)
ORDER BY country

score 0 · Answer 4 · edited Feb 26 '22 at 09:52

Here you have already done Group by Country.name so you can just have single country detail, so instead of going for the MAX(population) you can just do order by city.population also remove the group by for city.name

E.g.

SELECT Country.Name, city.name, city.population Population
FROM city
Join Country
On Country.Code=City.countrycode
WHERE city.population IS NOT NULL
GROUP BY Country.Name
ORDER BY city.population desc;

This will not give you the countries in sorted order but that can also be done after adding another order by on top of it if you really want country name also sorted.

SELECT Country.Name, city.name, city.population Population
FROM city
Join Country
On Country.Code=City.countrycode
WHERE city.population IS NOT NULL
GROUP BY Country.Name
ORDER BY country.name, city.population desc;

Hope that helps to simplify the SQL query. This I have tested in MySQL.

score 0 · Answer 5 · answered Jun 19 '23 at 14:17

0

write SQL Query

Highest Population Joins - SQL

In the city_populations dataset, add a column which tells the rank of city in terms of population. City with highest population should get rank = 1

Table name : city populations

1.Column:city New york Los Angeles Chicago

2.Column:state NY CA IL

3.Column:population estimate_2012 8336697 3857799 2714856

4.Column:id 1 2 3

You have to write select queries from table city_populations

answered Jun 19 '23 at 14:17

Yogesh Patil

1

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/34568637) – bitmagier Jun 23 '23 at 11:14

Cr1xus · Answer 6 · 2015-11-17T05:53:32.657

-1

You cannot use MAX in multiple select try this:

SELECT Country.Name, city.name, city.population
FROM city
Join Country
On Country.Code=City.Country
WHERE city.population IS NOT NULL and city.population in (SELECT MAX(population) FROM city limit 1)
GROUP BY Country.Name, city.name
ORDER BY Country.Name;

edited Nov 17 '15 at 05:53

answered Nov 17 '15 at 05:29

Cr1xus

427
3
20

And what if two cities have the same population? You will double your result set. – Lock Nov 17 '15 at 05:33
you do not want that? – Cr1xus Nov 17 '15 at 05:34
just add limit 1 after order by country.name – Cr1xus Nov 17 '15 at 05:35
which will give you a single row for the whole query, not a single row per country. – Lock Nov 17 '15 at 05:49
1

@Cr1xus `LIMIT` is not supported syntax in Oracle. – Lalit Kumar B Nov 17 '15 at 05:54

For each country, find city that has the highest population and the city's population

6 Answers6