Sorting in group_concat

Question

Data:

id  uid     type

1   20      A
2   20      B
3   20      A
4   6       A
5   1       A
6   3       A
7   6       A
8   1       B

Scenario:

I want to group by type and sort it by id. I am using group by to group the uid.

Current Query:

SELECT
    type,
    GROUP_CONCAT(DISTINCT uid) AS users,
    COUNT(type) AS typeCount
FROM
    `test2`
GROUP BY
    type

Problem:

But the order of the uid is incorrect, it should be in descending order according to id.

Expected Result:

type    users       typeCount
A       6,3,1,20    6
B       1,20        2

My results:

type    users       typeCount
A       20,6,1,3    6
B       20,1        2

Answer 1

The mistery of MySQL.

Actually the engine takes first value in ASC order, no matter that you are asking for DESC by ID, so first "flip" the table, then:

SELECT
    type,
    GROUP_CONCAT(DISTINCT uid ORDER BY id DESC) AS users,
    COUNT(type) AS typeCount
FROM
    (SELECT * FROM `test2` ORDER BY id DESC) test2
GROUP BY
    type

SQLFiddleDemo

Answer 2

The answer from @mitkosoft is already right.

I am posting this just to analyze the right expected result.

From the following output, we can see that, for type 'A' group, before DISTINCT taking effect, after ORDER BY id DESC, the rows are:

6 3 1 6 20 20

Then DISTINCT can produce two possible results: 6,3,1,20 or 3,1,6,20.

Which one is produced is undetermined and realization related. Otherwise, we can't rely on that.

Therefore, the expect result for group 'A' should be 6,3,1,20 or 3,1,6,20. Both correct.

mysql> SELECT * FROM test2;
+------+------+------+
| id   | uid  | type |
+------+------+------+
|    1 |   20 | A    |
|    2 |   20 | B    |
|    3 |   20 | A    |
|    4 |    6 | A    |
|    5 |    1 | A    |
|    6 |    3 | A    |
|    7 |    6 | A    |
|    8 |    1 | B    |
+------+------+------+
8 rows in set (0.00 sec)

mysql> SELECT uid FROM test2 WHERE type='A' ORDER BY id DESC;
+------+
| uid  |
+------+
|    6 |
|    3 |
|    1 |
|    6 |
|   20 |
|   20 |
+------+
6 rows in set (0.00 sec)