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.