CODEWITHSUNDEEP
sqlsubquery
user
user

Reputation: 253

Can't solve a problem where subquery is needed

Here is the problem: Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

This is how I tried to do it, it is incorrect, the sql probably can't set apart these two populations as belonging to different countries, but I have no idea how to proceed.

My first take on it:

SELECT name, continent
FROM world
GROUP BY continent
WHERE (population/3) >= population;

This is my second try:

select name, continent
from world x
where x.population in (select population from world where 
continent=x.continent and x.population>=(3*population))

Upvotes: 0

Views: 67

Answers (1)

forpas
forpas

Reputation: 164089

One way to do it is with ALL operator:

select t.name, t.continent
from world t
where (t.population / 3) > ALL (
  select population from world 
  where continent = t.continent and name <> t.name 
)

The subquery contains all the populations of the countries in the same continent as the country of the row that you want to compare (except that country).
So if population / 3 of that country is greater than all these populations then that country is selected.
Or similarly:

select t.name, t.continent
from world t
where (t.population / 3) > (
  select max(population) from world 
  where continent = t.continent and name <> t.name 
)

Upvotes: 1

Related Questions