Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL - SELECT the name that comes first alphabetically

Tags:

sql

mysql

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           |
+---------------+---------------------+
like image 983
Dylan Avatar asked May 14 '15 07:05

Dylan


People also ask

How do I sort names in alphabetical order in MySQL?

The MySQL ORDER BY Keyword The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

What do you call reverse alphabetical order in SQL jargon?

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.


2 Answers

This is a simple aggegation:

SELECT continent, MIN(name) AS name
FROM world 
GROUP BY continent
ORDER by continent
like image 52
dnoeth Avatar answered Oct 10 '22 16:10

dnoeth


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.

like image 21
Oleg Kuts Avatar answered Oct 10 '22 17:10

Oleg Kuts