SQL query on self joins

Question

Given a table, world with 3 cols: country, continent, population how do i get an output that shows the countries for each continent with the largest population?

Here is my code:

select tb1.country, tb1.population from world tb1 join world tb2
ON tb1.continent=tb2.continent
Where tb1.population>tb2.population;

my output only returns me 1 country from 1 continent.

The question is awkward,what do you mean `countries for each continent with the largest population`?Largest population of a continent?First x countries per continent in population? — Mihai, Feb 11 '15 at 21:54
using MAX() and GROUP BY maybe ? some sample table data would be helpful here if you could provide that. — Maximus2012, Feb 11 '15 at 21:55
Top country from each continent, or each country for continent with the highest population? — Hart CO, Feb 11 '15 at 22:21
There are lots of ways to skin this cat. The solutions documented in the manual also work well. — Strawberry, Feb 11 '15 at 23:49

Phil · Answer 1 · 2015-02-11T22:37:50.223

1

I've used this approach before and it seems to work well

SELECT a.country, a.population
FROM world a
WHERE NOT EXISTS (
    SELECT 1 FROM world b
    WHERE b.continent = a.continent
    AND b.population > a.population
)

SQL Fiddle

_{Be patient, SQL Fiddle seems really slow today}

To elaborate, the sub-query acts as a filter for the outer query, eliminating any rows (via NOT EXISTS) per continent where a country with a greater population exists.

This also returns countries with equal maximum populations. If you need only one country per continent, you would need some further elimination data.

edited Feb 11 '15 at 22:37

answered Feb 11 '15 at 21:57

Phil

157,677
23
242
245

what does the 1 mean? – jxn Feb 11 '15 at 22:19
It's just *"any value"*. The sub-query acts as a filter. We're not interested in any data from it, just whether or not it has any results. You could use `SELECT *` but many years ago, that was less performant. Old habits die hard – Phil Feb 11 '15 at 22:24
@Jenn more info here ~ http://stackoverflow.com/questions/7039938/what-does-select-1-from-do – Phil Feb 11 '15 at 22:32

score 0 · Answer 2 · edited Feb 11 '15 at 22:21

0

The answer could be the following:

select w1.country, w1.population from world w1,
(select max(w2.population) as p from world w2 group by w2.continental) T
where w1.population = T.p

edited Feb 11 '15 at 22:21

Taryn East

27,486
9
86
108

answered Feb 11 '15 at 22:17

yanet.moralesr

3
5

1

Hiya, this may well solve the problem... but it'd be good if you could edit your answer and provide a little explanation about how and why it works :) Don't forget - there are heaps of newbies on Stack overflow, and they could learn a thing or two from your expertise - what's obvious to you might not be so to them. – Taryn East Feb 11 '15 at 22:22
Also just fyi, you can select all your code and hit the `{}` button to format it as code. – Hart CO Feb 11 '15 at 22:23

MT0 · Answer 3 · 2015-02-11T22:58:43.903

Since MySQL does not have analytic functions you can fake it with GROUP_CONCAT:

SELECT
  Continent,
  SUBSTRING_INDEX( CONCAT( GROUP_CONCAT(Country ORDER BY Population DESC), '|'), '|', 1) AS Country,
  SUBSTRING_INDEX( CONCAT( GROUP_CONCAT(Population ORDER BY Population DESC), ','), ',', 1) AS Population
FROM Populations
GROUP BY Continent;

Alternatively you can use:

SELECT
  p.Continent,
  p.Country,
  p.Population
FROM Populations p
     INNER JOIN
     (
       SELECT Continent,
              MAX( Population ) AS max_pop
       FROM Populations
       GROUP BY Continent
     ) m
     ON (   p.continent  = m.continent
        AND p.population = m.max_pop )
GROUP BY Continent;

Or:

SELECT
  p.Continent,
  p.Country,
  p.Population
FROM Populations p
WHERE p.population =
       (
         SELECT MAX( Population )
         FROM   Populations m
         WHERE  p.continent = m.continent
       );

SQLFIDDLE

SQL query on self joins

3 Answers3