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

"I want to group by type and get all the uid in descending order" "it should be in descending order according to id." inconsistent. — Dylan Su, Feb 19 '16 at 11:45

score 6 · Accepted Answer · edited Feb 19 '16 at 11:09

6

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

edited Feb 19 '16 at 11:09

Lukasz Szozda

162,964
23
234
275

answered Feb 19 '16 at 11:01

mitkosoft

5,262
1
13
31

1

it's giving your expected result. – mitkosoft Feb 19 '16 at 11:03
No, its giving `3,1,6,20` – Shaharyar Feb 19 '16 at 11:04
On my MySQL 5.6.17 returns A: 6,3,1,20 – mitkosoft Feb 19 '16 at 11:06
The fiddle works fine, I have the same version. I don't understand, do you have any idea? – Shaharyar Feb 19 '16 at 11:13
1

Your second paragraph is simply wrong and the use of a subquery is unnecessary and misleading. – Gordon Linoff Feb 19 '16 at 11:41
not agree - mysql perform table scan in asc order no matter that you have desc order request into group_concat. without flipping the table, the query will return 3,1,6,20 which is not the expected result. – mitkosoft Feb 19 '16 at 11:45

score 3 · Answer 2 · answered Feb 19 '16 at 11:55

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)

score 1 · Answer 3 · edited May 23 '17 at 12:31

You can do something like Sampson suggested in this post:

MySQL: Sort GROUP_CONCAT values

Here is link to MySQL docs

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function%5Fgroup-concat

Here is the example he given:

SELECT student_name,
  GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ')
  FROM student
  GROUP BY student_name;

You just need to adjust it to your needs.

Hope this helps

score 1 · Answer 4 · answered Feb 19 '16 at 11:41

No subquery is needed for this this. From your description you simply need an ORDER BY in the GROUP_CONCAT():

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

In MySQL, it is a good idea to avoid unnecessary subqueries, because the database engine materializes them.

score 0 · Answer 5 · answered Feb 19 '16 at 10:37

0

Try something like:

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

answered Feb 19 '16 at 10:37

SMA

36,381
8
49
73

Sorting in group_concat

5 Answers5