After an ORDER BY get the first item in MySQL

Question

I'm using a GROUP BY to display general info from a table.

SELECT * FROM table GROUP BY Continent

Datas are the following ones:

|--------|-----------|-----------|
| Id     | Continent |    Fruits |
|--------|-----------|-----------|
|      1 |    Africa |    Banana |
|      1 |    Africa |    Cherry |
|      1 |    Mexico |     Apple |
|      1 |    Mexico |      Pear |
|      1 |    Europa | Blueberry |
|      1 |    Europa |    Orange |
|      1 |    Europa |      Kiwi |
|--------|-----------|-----------|

Now how can I get the FIRST fruit from each continent ?

This is what I've tried:

SELECT *, MIN(Fruits) AS FirstFruit FROM table GROUP BY Continent

Thanks.

Answer 1

If you just want two columns in the resultset, then aggregation is sufficient:

select continent, min(fruits) as firstfruit 
from mytable 
group by continent

If, on the other hand, there are other columns you are interested it, then aggregation alone cannot give you the result you want. One option uses a correlated subquery to filter the table:

select t.*
from mytable t
where t.fruits = (select min(t1.fruits) from mytable t1 where t1.continent = t.continent)