Selecting top n from subquery

Question

Suppose I have a table world(continent, country).

How can I filter this table to include just the continent and country of the first five countries in each continent (alphabetically)? If I want to select only the first country alphabetically, I simply do:

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

But if I try to get more than one country with

SELECT continent, country
FROM world x WHERE country in (
    SELECT country
    FROM world y
    WHERE x.continent = y.continent
    ORDER BY country asc
    LIMIT 5
)

then an error is thrown:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

What is an alternative query that I can run?

score 2 · Accepted Answer · answered Apr 04 '18 at 03:47

2

For each row, count how many countries are before it on the list:

SELECT continent, country
FROM world x 
WHERE 5 > (
    SELECT count(*) 
    FROM world y
    WHERE x.continent = y.continent
    AND x.country > y.country
)

answered Apr 04 '18 at 03:47

Turophile

3,367
1
13
21

Selecting top n from subquery

1 Answers1