Select the first row of each group in MySQL

4

Suppose I have a table like this:

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

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

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

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

Then, after the ordering, 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, should look like this:

GROUP BY group

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

But do not stay! Whenever I try to do these commands, MySQL ignores the sort command and groups it as if the table had not been re-ordered before, being grouped like this:

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 do not work in MySQL.

PS2: Note that my table has no numbers, only strings. With numbers it could be easier, but the intent is to generalize the solution to the same string.

Thank you in advance!

    
asked by anonymous 27.08.2018 / 04:03

1 answer

6

I created a test fiddle and the response given in comment by @Anthony Accioly caters to what you need:

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

edited:

Considering the comments, I believe this answers . Detail: The solution will only be correct if the item_1 field 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); 
    
27.08.2018 / 14:22