Search code examples
sqlpostgresqlgreatest-n-per-group

getting data from 2 tables and writing a query

I am having trouble with a query in php. I don't seem to get the output that I am wanting to get. The query that I am trying to write is

For each country, list the largest population of any of its cities and the name of that city. Order the results in decreasing order of city populations.

Here is the code that I tried, along with the table that I tried:

SELECT MAX(population) as population, name
FROM what.city
ORDER BY population DESC

Here are the tables that I am using:

               Table "what.country"
     Column      |         Type          |               Modifiers              
-----------------+-----------------------+--------------------------------------
 country_code    | character(3)          | not null default ''::bpchar
 name            | character varying(52) | not null default ''::character varying
 continent       | continent             | not null
 region          | character varying(26) | not null default ''::character varying
 surface_area    | real                  | not null default 0::real
 indep_year      | smallint              | 
 population      | integer               | not null default 0
 life_expectancy | real                  | 
 gnp             | real                  | 
 gnp_old         | real                  | 
 local_name      | character varying(45) | not null default ''::character varying
 government_form | character varying(45) | not null default ''::character varying

               Table "what.city"
    Column    |         Type          |                     Modifiers                    
--------------+-----------------------+-----------------------------------------
 id           | integer               | not null default nextval('city_id_seq'::regclass)
 name         | character varying(35) | not null default ''::character varying
 country_code | character(3)          | not null default ''::bpchar
 district     | character varying(20) | not null default ''::character varying
 population   | integer               | not null default 0
Solution

  • The currently accepted answer is incorrect. One simple and fast way (among others) to achieve this in Postgres is with DISTINCT ON:

    SELECT co.name AS country_name, ci.city_name, population
FROM  (
   SELECT DISTINCT ON (country_code)
          country_code, name AS city_name, population
   FROM   what.city
   ORDER  BY country_code, population DESC
   ) ci
JOIN  what.coutry co USING (country_code);

    Details: