Reputation: 1509
I'm new to mysql & just started learning it. Last night I was trying to re-form following sub-query on country table of world database, into a join.
SELECT continent, NAME, population FROM country c WHERE
population = (SELECT MAX(population) FROM country c2
WHERE c.continent=c2.continent AND population > 0)
I tried following query and several others with inner join etc. but failed. I'm getting result with the following query where max population is as expected but continent & country name as different.
SELECT c.continent, c2.name, MAX(c2.population) AS pop FROM country c, country c2
WHERE c.continent = c2.continent GROUP BY continent
Please help, how can I get same result as the sub-query above.
Thanks in advance
Upvotes: 0
Views: 308
Reputation: 79979
You should get the MAX(population)
with GROUP BY continent
inside a subquery, then JOIN
it with the table itself; Like this:
SELECT c1.continent, c1.NAME, c1.population
FROM country c1
INNER JOIN
(
SELECT continent, MAX(population) AS Maxp
FROM country
WHERE population > 0
GROUP BY continent
) AS c2 ON c1.population = c2.maxp
AND c1.continent = c2.continent;
Upvotes: 1