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 ?
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.”
Answered By – OLIVER.KOO
Answer Checked By – Terry (BugsFixing Volunteer)