I am running the examples to be found here: https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial
I am given a table named world:
I would expect this:
Why do I get this result, and how can I correct my SQL query?
use
SELECT distinct continent,
( SELECT name
FROM world b
WHERE a.continent = b.continent
ORDER BY name
LIMIT 1)
FROM world a
or
SELECT continent,
( SELECT name
FROM world b
WHERE a.continent = b.continent
ORDER BY name
LIMIT 1)
FROM world a
GROUP BY continent
You are getting a row for every country in the world and there are many per continent - -you need to limit to just one continent returned in the result set.
This technique is called a correlated sub-query, if you were wondering.