user391986 user391986 - 5 months ago 20
MySQL Question

order by within group concat

The order by is not working in the second query.
I need to order by first DNAID then DNBID

First Query its ordered as:
111221

Second Query its ordered as:
112112

for more info and details about what im trying to accomplish
database query group union find latest

mysql> select * from metarun;
+----+------------+-------+-------+--------------+----------+
| ID | RunGroupID | DNAID | DNBID | CONFIGTYPEID | DateTime |
+----+------------+-------+-------+--------------+----------+
| 1 | 1 | 1 | 1 | 2 | NULL |
| 2 | 1 | 1 | 2 | 2 | NULL |
| 3 | 1 | 2 | 1 | 2 | NULL |
| 4 | 2 | 1 | 1 | 4 | NULL |
| 5 | 2 | 3 | 2 | 4 | NULL |
| 6 | 3 | 3 | 2 | 1 | NULL |
| 7 | 3 | 1 | 2 | 1 | NULL |
| 8 | 3 | 3 | 1 | 1 | NULL |
| 9 | 4 | 1 | 1 | 3 | NULL |
| 10 | 4 | 2 | 1 | 3 | NULL |
| 11 | 4 | 1 | 2 | 3 | NULL |
| 12 | 5 | 1 | 1 | 2 | NULL |
| 13 | 5 | 2 | 1 | 2 | NULL |
| 14 | 5 | 1 | 2 | 2 | NULL |
| 15 | 6 | 3 | 2 | 4 | NULL |
| 16 | 6 | 1 | 1 | 4 | NULL |
+----+------------+-------+-------+--------------+----------+



mysql> SELECT RunGroupID, GROUP_CONCAT(DNAID, DNBID SEPARATOR ''), ConfigTypeID, DateTime
-> FROM metarun
-> GROUP BY RunGroupID
-> ORDER BY RunGroupID, DNAID ASC, DNBID ASC;
+------------+-----------------------------------------+--------------+----------+
| RunGroupID | GROUP_CONCAT(DNAID, DNBID SEPARATOR '') | ConfigTypeID | DateTime |
+------------+-----------------------------------------+--------------+----------+
| 1 | 111221 | 2 | NULL |
| 2 | 1132 | 4 | NULL |
| 3 | 123132 | 1 | NULL |
| 4 | 111221 | 3 | NULL |
| 5 | 111221 | 2 | NULL |
| 6 | 1132 | 4 | NULL |
+------------+-----------------------------------------+--------------+----------+
6 rows in set (0.00 sec)

mysql> SELECT RunGroupID, GROUP_CONCAT(DNAID, DNBID SEPARATOR ''), ConfigTypeID, DateTime
-> FROM metarun
-> WHERE configtypeid=2
-> GROUP BY RunGroupID
-> ORDER BY RunGroupID;
+------------+-----------------------------------------+--------------+----------+
| RunGroupID | GROUP_CONCAT(DNAID, DNBID SEPARATOR '') | ConfigTypeID | DateTime |
+------------+-----------------------------------------+--------------+----------+
| 1 | 111221 | 2 | NULL |
| 5 | 112112 | 2 | NULL |
+------------+-----------------------------------------+--------------+----------+
2 rows in set (0.02 sec)

Answer

I believe you can specify an ORDER BY clause in the GROUP_CONCAT function:

SELECT RunGroupID, GROUP_CONCAT(DNAID, DNBID ORDER BY DNAID, DNBID SEPARATOR '')