Query Returning Too Many Results?

Question

I am trying to get the largest population of any city in each country in a query. I need to join the city and country tables, as they are located on two tables, and join them through the country code.

I have the following query:

SELECT country.name         AS country, 
       city.name            AS city, 
       Max(city.population) AS max_pop 
FROM   country 
       INNER JOIN city 
               ON( country.country_code = city.country_code ) 
GROUP  BY country.name, 
          city.name 
ORDER  BY country.name ASC;

My thought process was to get my country name, city name, and max from the joined table. I assumed and have tested, that max will give me only one result, however in this case its giving me several! I have both city and country name in my group by in order to get it to run.

Thoughts?

Sebas · Accepted Answer · 2013-03-12T02:26:06.907

2

SELECT co.name         AS country,
       ct.name         AS city, 
       t.pop AS max_pop 
FROM country AS co
      INNER JOIN (
               SELECT country_code, Max(population) AS pop FROM city GROUP BY country_code
             ) t ON co.country_code = t.country_code 
      INNER JOIN city AS ct ON ct.population = t.pop AND co.country_code = ct.country_code 
ORDER  BY country.name ASC;

edited Mar 12 '13 at 02:26

answered Mar 12 '13 at 02:21

Sebas

21,192
9
55
109

you have no city.name in nested query – guido Mar 12 '13 at 02:21
i dont mean in the group by predicate (that's correct) but you select it in the outer query but its not among the columns after the join – guido Mar 12 '13 at 02:23
This can be much simpler. I added an answer. – Erwin Brandstetter Mar 12 '13 at 03:42

score 2 · Answer 2 · edited May 23 '17 at 10:24

Much shorter and faster with DISTINCT ON (PostgreSQL extension to the SQL standard):

SELECT DISTINCT ON (1)
       co.name       AS country
      ,ci.name       AS city
      ,ci.population AS max_pop
       -- add more columns as you please
FROM   country co
JOIN   city    ci USING (country_code)
ORDER  BY 1, 3 DESC, 2;

If two cities have the equally largest population in one country, I pick the alphabetically first in this case. That's why I added the positional parameter 2 (for ci.name) to the ORDER BY clause.

I also simplified with table aliases and a USING equi-join.

About DISTINCT ON:

Select first row in each GROUP BY group?

Oh, I don't know postgreSQL enough, I didn't know DISTINCT ON existed. Thank you for sharing. — Sebas, Mar 12 '13 at 12:03

Query Returning Too Many Results?

2 Answers2