Select the first row of each group in Mysql

Question

Suppose I have such a table:

item   | group
-------+--------
item a | group x
item b | group y
item c | group y
item d | group x
item e | group z

And that I want to select a single item to represent each group.

Now, you might be thinking, "Okay, but which item? Because each group has several!"

So, the rule is as follows: I simply want to sort my table according to some column, which in this specific case will be the item column, and then associate the first row of each group with the group itself, through the GROUP BY command (or other more suitable).

Then, after the ordination, it would look like this:

ORDER BY item DESC

item   | group
-------+--------
item e | group z
item d | group x
item c | group y
item b | group y
item a | group x

And grouping according to the group column, it should look like this:

GROUP BY group

item   | group
-------+--------
item e | group z
item d | group x
item c | group y

But it won’t be! Whenever I try to do these commands, Mysql ignores the sorting command and groups as if the table had not been reoorded before, thus being grouped together:

item   | group
-------+--------
item a | group x
item b | group y
item e | group z

PS1: I saw some solutions to this problem through the OVER and PARTITION BY command. But they don’t work in Mysql.

PS2: Note that my table does not have Numbers, only strings. With numbers could be easier, but the intention is to generalize the solution to string even.

Thanks in advance!

Answer 1

I created a test fiddle and the reply given in comment by @Anthony Accioly meets what you need:

SELECT MAX(item), grupo 
FROM tabela 
GROUP BY grupo

edited:

Considering the comments, I believe that this answer.
Detail: the solution will only be correct if the field item_1 is unique.

SELECT item_1, item_2, grupo 
FROM tabela t1 
WHERE t1.item_1 = (select max(t2.item_1) from tabela t2 where t2.grupo = t1.grupo);