Ordering in a MySQL GROUP_CONCAT with a function in it

Question

I want to order the results in a GROUP_CONCAT function. The problem is, that the selection in the GROUP_CONCAT-function is another function, like this (fantasy select):

SELECT a.name,
    GROUP_CONCAT(DISTINCT CONCAT_WS(':', b.id, c.name) ORDER BY b.id ASC) AS course
FROM people a, stuff b, courses c
GROUP BY a.id

I want to get a result like (ordered by b.id):

michael    1:science,2:maths,3:physics

but I get:

michael    2:maths,1:science,3:physics

Does anyone know how I can order by b.id in my group_concat here?

[GROUP_CONCAT](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) supports it's own `ORDER BY` clause... Not knowing what the user defined function is, or does, will not help us to be able to help you. — OMG Ponies, Jun 18 '11 at 22:42
It's just like I wrote: the function "concat_ws" within the function "group_concat". I never said it was a user defined function. — acme, Jun 20 '11 at 14:05
It's unclear how to write a valid solution, as the table structure, content and relationships between the tables is unspecified (i.e. there are no join specifications). — outis, Jan 31 '13 at 23:20
@acme Could mark my answer as the correct answer if it is the correct answer? It seems to be very helpful to others. — Jazzy, Jun 11 '14 at 20:59

Jazzy · Accepted Answer · 2012-11-03T21:16:30.420

43

If anyone cares, I think I found a solution for at least a similar problem.

http://mahmudahsan.wordpress.com/2008/08/27/mysql-the-group_concat-function/

select GROUP_CONCAT(columnName order by someColumn SEPARATOR '|') from tableName where fieldId = p.id

The order by goes in the group_concat BEFORE the separator if there is one.

edited Nov 03 '12 at 21:16

answered Nov 03 '12 at 02:18

Jazzy

6,029
11
50
74

Good find!! Thanks for sharing! – rckehoe Dec 18 '13 at 13:58
Awesome, This one saved my life – Nadeeshaan Aug 03 '14 at 15:46
Nice one!!Thanks for sharing – Ganesh RJ Sep 18 '15 at 10:23
The same info found [in the docs](http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat). `SELECT student_name, GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ') FROM student GROUP BY student_name`; – Cvetan Nenov Nov 06 '15 at 17:53
1

And if `columnName` and `someColumn` are the same, you can just do a `GROUP_CONCAT(columnName ORDER BY 1 SEPARATOR '|')`. – Doin Dec 08 '15 at 11:40
This fails to solve the critical problem asked. How does one order by a Distinct field when that Distinct is done on a function? "(DISTINCT CONCAT_WS(':', b.id, c.name) ORDER BY b.id ASC)" -- b.id is lost from association after CONCAT_WS(...). How does one reference that? – HoldOffHunger Jul 18 '16 at 17:05

score 3 · Answer 2 · answered Nov 04 '15 at 20:53

3

I know this is really old, but just now I was looking for an answer and @korny's answer gave me the idea for this:

SELECT a.name,
GROUP_CONCAT(DISTINCT CONCAT_WS(':', b.id, c.name) 
             ORDER BY CONCAT_WS(':', b.id, c.name) ASC) AS course
FROM people a, stuff b, courses c
GROUP BY a.id

(And it works for me, if that wasn't clear :-) )

answered Nov 04 '15 at 20:53

AMeiri

51
3

1

I'm not absolutely sure, but I believe this is the same as using `GROUP_CONCAT(DISTINCT CONCAT_WS(':', b.id, c.name) ORDER BY 1)`. Which is much simpler, if it works! – Doin Dec 08 '15 at 11:21
Yes! The actually-correct answer!!! (Of course nobody's doing exactly what the OP is! We're on this page because we want to know how to reference the results of DISTINCT Func(...) in the Order By, and the answer is you just need to call the Func again.) – HoldOffHunger Jul 18 '16 at 20:20

score 2 · Answer 3 · answered Oct 16 '09 at 13:25

2

I don't know of a standard way to do this. This query works, but I'm afraid it just depends on some implementation detail:

SELECT a_name, group_concat(b_id)
FROM (
    SELECT a.name AS a_name, b.id AS b_id
    FROM tbl1 a, tbl2 b
    ORDER BY a.name, b.id) a
GROUP BY a_name

answered Oct 16 '09 at 13:25

Lukáš Lalinský

40,587
6
104
126

After a little research it seems what I ask is not possible. Thanks nevertheless! – acme Oct 19 '09 at 07:05

score 2 · Answer 4 · edited Feb 04 '14 at 13:29

2

No need for subselects.

SELECT people.name, CONCAT_WS(":", stuff.id, courses.name) data
FROM people, stuff, courses
ORDER BY stuff.id, people.name

edited Feb 04 '14 at 13:29

acme

14,654
7
75
109

answered Jan 22 '14 at 20:42

Sandro Frattura

41
1

score 0 · Answer 5 · answered Sep 26 '12 at 21:52

0

SELECT generated.name, GROUP_CONCAT(generated.data)
FROM (
    SELECT people.name, CONCAST_WS(":", stuff.id, courses.name) data
    FROM people, stuff, courses
    ORDER BY stuff.id, people.name
) generated
GROUP BY generated.name

answered Sep 26 '12 at 21:52

Izkata

8,961
2
40
50

score 0 · Answer 6 · edited Jun 18 '11 at 22:38

0

SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id)
FROM book_mast
GROUP BY pub_id
ORDER BY GROUP_CONCAT(DISTINCT cate_id) ASC;

edited Jun 18 '11 at 22:38

Andriy M

76,112
17
94
154

answered Jun 18 '11 at 19:57

korny

1

1

I want to order the aggregated elements IN the `GROUP_CONCAT` not the whole results. – acme Apr 04 '12 at 11:47

Ordering in a MySQL GROUP_CONCAT with a function in it

6 Answers6