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!