CODEWITHSUNDEEP
sqldatabaseoracle-database
LockSkywalker
LockSkywalker

Reputation: 47

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

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.

Upvotes: 1

Views: 19245

Answers (6)

Yogesh Patil
Yogesh Patil

Reputation: 1

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

Upvotes: 0

shashank shekhar Kaushik
shashank shekhar Kaushik

Reputation: 1

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.

Upvotes: 0

daintym0sh
daintym0sh

Reputation: 31

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

Upvotes: 0

Cr1xus
Cr1xus

Reputation: 437

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;

Upvotes: -1

Kai
Kai

Reputation: 352

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.

Upvotes: 2

Lock
Lock

Reputation: 5522

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

Upvotes: 3

Related Questions