?
Solved

order by within group concat

Posted on 2011-02-22
4
Medium Priority
?
338 Views
Last Modified: 2012-08-13
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
http://www.experts-exchange.com/Database/MySQL/Q_26839744.html                     
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)

Open in new window

0
Comment
Question by:NewtonianB
  • 2
3 Comments
 

Author Comment

by:NewtonianB
ID: 34956758
Hi Kent, its related but a separate and much more specific question here im asking help on a specific experimental implementation while other post im asking for ideas on implementations
0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 total points
ID: 34956853


Ok.  Just an 'ORDER BY DNAID, DNBID' should do it.  Can you post your query?


Kent
0
 

Author Comment

by:NewtonianB
ID: 34958058
Hi Kdo, everything is attached above what else were you asking exactly?
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question