CODEWITHSUNDEEP
mysqlsql
Dylan
Dylan

Reputation: 955

MySQL - SELECT the name that comes first alphabetically

I have started to learn MySQL.

Here is the table world:

+-------------+-----------+---------+
|    name     | continent |  area   |
+-------------+-----------+---------+
| Afghanistan | Asia      | 652230  |
| Albania     | Europe    | 2831741 |
| Algeria     | Africa    | 28748   |
| ...         | ...       | ...     |
+-------------+-----------+---------+

I need:

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

The result of SELECT must be:

+---------------+---------------------+
|   continent   |         name        |
+---------------+---------------------+
| Africa        | Algeria             |
| Asia          | Afghanistan         |
| Caribbean     | Antigua and Barbuda |
| Eurasia       | Armenia             |
| Europe        | Albania             |
| North America | Belize              |
| Oceania       | Australia           |
| South America | Argentina           |
+---------------+---------------------+

Upvotes: 16

Views: 29473

Answers (12)

Apoorv Mehta
Apoorv Mehta

Reputation: 102

select continent, name
from
(select continent, name, rank() over(partition by continent order by name) r1
from world) a
where a.r1 = 1

Upvotes: 0

Ritika
Ritika

Reputation: 1

Select distinct continent, name from world x 
where name <=All (Select name from world y where x.continent=y.continent)

Upvotes: -1

Sarth
Sarth

Reputation: 266

SELECT continent,
       name
FROM world x
WHERE name=
    (SELECT name
     FROM world y
     WHERE x.continent=y.continent
     ORDER BY name
     LIMIT 1)

This is correlated/ synchronous query.

Upvotes: 0

mazen
mazen

Reputation: 11

SELECT  distinct x.continent , x.name
FROM world x ,world y 
WHERE x.name = (SELECT y.name FROM world y
 WHERE y.continent=x.continent order by y.name asc limit 1 ) ;

Upvotes: 1

Chhun Panharath
Chhun Panharath

Reputation: 136

what about this sql :

select distinct continent, 
      (select name 
       from world y where y.continent = x.continent limit 1 ) as name 
from world x

Upvotes: 0

Dmitry Surkis
Dmitry Surkis

Reputation: 31

The SqlZoo solution would better look like this:

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

Upvotes: 3

Oleg Kuts
Oleg Kuts

Reputation: 829

If it's an Exercise from SQLZoo, than IMO it should look something like this:

select continent, name from world x
where name = (select name 
                  from world y 
                  where  x.continent =  y.continent 
                  order by name asc 
                  limit 1)

P.S. I study SQL from there now and this post helped me. Thanks to @Parado!)

Update: I've found this site with answers. Useful if stack.

Upvotes: 13

user4990905
user4990905

Reputation: 1

select continent, name from world group by continent order by name

Upvotes: -2

Luis Alberto
Luis Alberto

Reputation: 13

If you need list each continent alphabetically, you have use

     SELECT * from world ORDER by continent

But, If you nedd list each country your have use

     SELECT * from world ORDER by name

Upvotes: -3

dnoeth
dnoeth

Reputation: 60462

This is a simple aggegation:

SELECT continent, MIN(name) AS name
FROM world 
GROUP BY continent
ORDER by continent

Upvotes: 30

Makis
Makis

Reputation: 1244

Try this

SELECT continent, name FROM world ORDER BY name ASC;

Upvotes: -3

Robert
Robert

Reputation: 25753

Try this

select distinct  w.continent, 
                 (select w2.name 
                  from world w2 
                  where  w.continent =  w2.continent 
                  order by name asc 
                  limit 1) name 
from world w
order by w.continent

Upvotes: 7

Related Questions