sqlselect

SQL query does not return expected result

I am running the examples to be found here: https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial

I am given a table named world:

enter image description here

enter image description here

I would expect this:

enter image description here

Why do I get this result, and how can I correct my SQL query?

Solution

  • 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.