'SQL ZOO List each continent and the name of the country that comes first alphabetically

Im confused why it could not be 

Select x.continent, x.name
From world x
Where x.name <= ALL (select y.name from world y where x.name=y.name)
ORDER BY name

Can anyone please explain to me why it has to be x.continent=y.continent and not x.name=y.name ?

Table

mysqlsqlcorrelated-subquery


Solution 1:[1]

when you use x.name=y.name you are comparing country name from x with the country name from y if both instances have the same country name. That basically would just return you the full table x.

You want to use x.continent=y.continent because you only want to compare the country name of an instance from x with the country name of an instance from y if they share the same continent.

Let me illustrate this step by step with an example: Here we have a table world and I populated with some data:

world: 

Select  x.continent, x.name
From world x
ORDER BY  name

continent       name
Asia            Afghanistan
Europe          Albania
Africa          Algeria  
Europe          Andorra
Africa          Angola
SouthAmerica    Bolivia
SouthAmerica    Brazil
Europe          Hungary
Asia            Japan
Africa          Nigeria
SouthAmerica    Peru
Asia            Taiwan

when you execute this query without the WHERE clause in your subquery:

Select  x.continent, x.name
From world x
Where x.name <= ALL (select y.name from world y)
ORDER BY name

you get this

continent   name
Asia        Afghanistan

This is because the where clause filtered out all but one country 

where x.name <= (Afghanistan,Taiwan,Japan,
                 Albania,Hungary,Algeria,Nigeria,Andorra,
                 Angola,Bolivia,Peru,Brazil)

and namely, the country name who comes first alphabetically which is Afghanistan.

but since we want to get the first country in each continent we will add x.continent=y.continent to our subquery

Select  x.continent, x.name
From world x
Where x.name <= ALL (select y.name from world y where x.continent=y.continent)
ORDER BY name

what is happening underneath is that now we are only comparing the country name of an instance from x with the country name of an instance from y if they share the same continent. So use continent of Asia for example:

Japen gets filtered out because Japan <= All(Afghanistan,Taiwan,Japan) is false since Japan is not less or equal to Afghanistan (A comes before J)

Taiwan gets filtered out because Taiwan <= All(Afghanistan,Taiwan,Japan) is false since Taiwan is not less or equal to Afghanistan.

Afghanistan does not get filtered out because Afghanistan <= All(Afghanistan,Taiwan,Japan) is true since Afghanistan is equal to Afghanistan

However, if you use x.name=y.name in your subquery then you are essentially comparing each country to itself and they will all get included in your final result set because all country name equals to the country name of itself.

I hope this help and welcome to Stack Overflow. If this answer or any other one solved your issue, please mark it as accepted."

Solution 2:[2]

I tried this and it also works:

SELECT continent, MIN(name)
FROM world
GROUP BY continent

Solution 3:[3]

This is the correct answer:

Select continent,name 
from world x
Where x.name <= ALL(select y.name from world y
                    where x.continent=y.continent)
ORDER BY continent

Solution 4:[4]

 select A.continent, A.name 
 from
 (Select  x.continent, x.name, RANK() OVER(PARTITION BY x.continent ORDER BY x.name 
 asc)AS Rank  
 From world x
 group by  x.continent, x.name)A
 where
 Rank = 1

Solution 5:[5]

This one is easy as min func will return first word in alphabetic order.

SELECT continent, name FROM world x WHERE name = (SELECT min(name) FROM world y WHERE x.continent = y.continent );

Solution 6:[6]

This also produce the correct answer.

SELECT continent, name
FROM world a
WHERE name <= ALL(SELECT name FROM world b
               WHERE a.continent = b.continent)

Solution 7:[7]

One note here - the ORDER BY is unnecessary for a correct answer to this problem, for the simplest solution all you need is:

SELECT continent, name
FROM world w1
WHERE w1.name <= ALL(SELECT name 
                       FROM world w2
                       WHERE w1.continent=w2.continent)

Solution 8:[8]

List each continent and the name of the country that comes first alphabetically.

Select continent, name from world x where name in (select min(name) from world y where x.continent= y.continent)

Solution 9:[9]

SELECT
    continent, name
FROM
    world x
WHERE
    name <= ALL(SELECT name FROM world y WHERE x.continent=y.continent ORDER BY name )
ORDER BY continent

return a list of ordered country names, then return the first entry of this list, finally order the outer select query by continent.

Solution 10:[10]

Another method use rank() funciton also works

select continent, name
from
(
    select continent, name, rank() over (partition by continent order by continent, name) as rn
    from world
    group by continent, name
    order by rn
) as a 
where rn = 1
order by continent

Solution 11:[11]

You Can query by this way

    SELECT MIN(continent), MIN(name) 
    FROM world
    GROUP By continent

Solution 12:[12]

with new as (
    select continent, name , row_number() over (partition by continent order by name) as row 
    from world
)

select continent,name
from new
where row = 1

Solution 13:[13]

The most simple query that will work for the question above:

Select continent, min(name) From world group by continent ;

Solution 14:[14]

SELECT continent, name 
FROM world as x
WHERE name = ALL(SELECT min(name)
                 FROM world y
                 WHERE x.continent = y.continent);

Related Questions