getting data from 2 tables and writing a query

Question

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

Ur missing group by. SELECT MAX(population) as population, name FROM what.city group by name ORDER BY max(population) DESC — radar, Oct 13 '14 at 00:07
It's helpful if you expand in " don't seem to get the output that I am wanting to get". i.e. tell us an error if you get one or describe what you are getting vs what you expect. — Nick.Mc, Oct 13 '14 at 02:24
@jimmy, can you unaccept my answer, Erwin has correct answer matching ur output — radar, Dec 05 '14 at 01:33

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

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:

Select first row in each GROUP BY group?

edited May 23 '17 at 11:50

Community

1
1

answered Dec 05 '14 at 01:20

Erwin Brandstetter

605,456
145
1,078
1,228

how to delete my answer, I was only helping with his query but your answer is helping the OP get what they want – radar Dec 05 '14 at 01:31
@radar: You can't delete an accepted answer. I flagged it for moderator attention. They will help out. – Erwin Brandstetter Dec 05 '14 at 01:40

getting data from 2 tables and writing a query

1 Answers1