GROUP CONCAT ORDER BY

Question

I ve a table like    ----------- ------- ------------    client id   views   percentage    ----------- ------- ------------            1       6           20             1       4           55             1       9           56             1       2           67             1       7           80             1       5           66             1       3           33             1       8           34             1       1           52     I tried group concat   SELECT li client id  group concat li views  AS views    group concat li percentage  FROM li GROUP BY client id    ----------- ------------------- -----------------------------    client id   views               group concat li percentage     ----------- ------------------- -----------------------------            1   6 4 9 2 7 5 3 8 1   20 55 56 67 80 66 33 34 52     ----------- ------------------- -----------------------------    But I want to get the views in order  like    ----------- ------------------- ----------------------------    client id   views               percentage                    ----------- ------------------- ----------------------------            1   1 2 3 4 5 6 7 8 9   52 67 33 55 66 20 80 34 56    ----------- ------------------- ----------------------------

User · Accepted Answer

You can use ORDER BY inside the GROUP CONCAT function in this way    SELECT li client id  group concat li percentage ORDER BY li views ASC  AS views   group concat li percentage ORDER BY li percentage ASC   FROM li GROUP BY client id

User · Answer

Try    SELECT li clientid  group concat li views ORDER BY li views  AS views         group concat li percentage ORDER BY li percentage   FROM table views li  GROUP BY client id   http   dev mysql com doc refman 5 0 en group-by-functions html function 5Fgroup-concat

User · Answer

The group concat supports its own order by clause   http   mahmudahsan wordpress com 2008 08 27 mysql-the-group concat-function   So you should be able to write   SELECT li clientid  group concat li views order by views  AS views  group concat li percentage order by percentage   FROM table views GROUP BY client id

User · Answer

In IMPALA  not having order in the GROUP CONCAT can be problematic  over at Coders Co  we have some sort of a workaround for that  we need it for Rax Impala   If you need the GROUP CONCAT result with an ORDER BY clause in IMPALA  take a look at this blog post  http   raxdb com blog sorting-by-regex

User · Answer

You can use SEPARATOR and ORDER BY inside the GROUP CONCAT function in this way  SELECT li client id  group concat li percentage ORDER BY li views ASC SEPARATOR       AS views  group concat li percentage ORDER BY li percentage ASC SEPARATOR      FROM li GROUP BY client id

[mysql] GROUP_CONCAT ORDER BY

Examples related to mysql

Examples related to sql-order-by

Examples related to group-concat