CODEWITHSUNDEEP
mysqlsqlgroup-bygroup-concat
Shaharyar
Shaharyar

Reputation: 12439

Sorting in group_concat

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

Upvotes: 5

Views: 2502

Answers (5)

Dylan Su
Dylan Su

Reputation: 6065

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)

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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.

Upvotes: 1

mitkosoft
mitkosoft

Reputation: 5316

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

Upvotes: 6

zachu
zachu

Reputation: 691

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

Upvotes: 1

SMA
SMA

Reputation: 37023

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

Upvotes: 0

Related Questions