CODEWITHSUNDEEP
sqldatabasepostgresql
Bill
Bill

Reputation: 61

How to select one row per country in query

I figured it out. SELECT DISTINCT ON (country.name) country.name AS country... worked

I am doing a query to get the single largest city (in terms of population) for each country. The problem is that some cities in a given country are tied at the same population, so the query is returning more than one city per country. (I have tried SELECT DISTINCT country.name, but it gave me the same output)

Any ideas?

Here is my code:

        $query = "SELECT country.name AS country, largest_city, sq.pop AS population
        FROM lab6.country INNER JOIN 

        (SELECT MAX(city.population) AS pop, country_code 
        FROM lab6.city 
        GROUP BY country_code) AS sq
        USING (country_code)

        INNER JOIN

        (SELECT city.name AS largest_city, city.population
        FROM lab6.city) AS sq1
        ON (sq1.population = sq.pop)

        ORDER BY country.name ASC";

Upvotes: 0

Views: 981

Answers (1)

Bill Gregg
Bill Gregg

Reputation: 7147

Per OP:

I figured it out. SELECT DISTINCT ON (country.name) country.name AS country... worked

Now we can see that this question has an answer without opening the question.

Upvotes: 1

Related Questions