I would like to be able to have my result set sorted by field id, and then filter out duplicate values on column name.
Given a table
+----+------+-------+
| id | name | other |
+----+------+-------+
| 1 | a | 1 |
| 2 | a | 0 |
| 3 | b | 0 |
| 4 | c | 0 |
+----+------+-------+
My sorting will be the following: 2, 4, 1, 3.
Consequently, I desire the result to look like:
+----+------+-------+
| id | name | other |
+----+------+-------+
| 2 | a | 0 |
| 4 | c | 0 |
| 3 | b | 0 |
+----+------+-------+
After all, in the sorted result set, the row with id = 2 appears before the row with id = 1, while they share a name. In such case, only the first name should be preserved.
In short:
SELECT *
FROM table
ORDER BY FIELD(id, 2, 4, 1, 3)
GROUP BY name
Unfortunately, it appears MySQL chooses which of the rows that turned out to contain a non-unique name it returns, regardless of the ordering done.
I have read this solution, but it only covers ordering by DESC (and indirectly ASC), but not by FIELD.
What would be the way to return only the first row in case of duplicates for a certain column while taking into account the sorting algorithm?
