CODEWITHSUNDEEP
mysqlsqlself-join
jxn
jxn

Reputation: 8025

SQL query on self joins

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.

Upvotes: 0

Views: 133

Answers (3)

MT0
MT0

Reputation: 167982

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

Upvotes: 0

Phil
Phil

Reputation: 164766

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.

Upvotes: 1

yanet.moralesr
yanet.moralesr

Reputation: 3

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

Upvotes: 0

Related Questions