Menu
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?
314
Introduction to MySQL GROUP_CONCAT() functionThe ORDER BY clause allows you to sort the values in ascending or descending order before concatenating. By default, it sorts the values in ascending order. If you want to sort the values in the descending order, you need to specify explicitly the DESC option.
The difference here is while CONCAT is used to combine values across columns, GROUP_CONCAT gives you the capability to combine values across rows. It's also important to note that both GROUP_CONCAT and CONCAT can be combined to return desired results.
The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value. Otherwise, it returns NULL.
GROUP_CONCAT is a function which concatenates/merges the data from multiple rows into one field. It is a GROUP BY function which returns a string if the group contains at least 1 non-null value, if it does not, it returns a Null value.
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.
186
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
