SELECT
c.Name,
cl.`Language`
FROM country c
INNER JOIN (
SELECT CountryCode, `Language`
FROM countrylanguage cl1
WHERE
Percentage = (
SELECT MAX(Percentage)
FROM countrylanguage cl2
WHERE
cl1.CountryCode = cl2.CountryCode
)
) cl ON c.Code = cl.CountryCode
| Name |
Language |
| Aruba |
Papiamento |
| Afghanistan |
Pashto |
| Angola |
Ovimbundu |
| Anguilla |
English |
| Albania |
Albaniana |
| Andorra |
Spanish |
| Netherlands Antilles |
Papiamento |
| United Arab Emirates |
Arabic |
| Argentina |
Spanish |
| Armenia |
Armenian |
| ... |
| The subquery selects the most popular language for each country and returns a table with the country code and language name. Then this table is connected to the main one using the INNER JOIN. |