Solved

order by within group concat

Posted on 2011-02-22
4
308 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
4 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 45

Accepted Solution

by:
Kdo earned 500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Error Code 2 19
SELECT query on two levels (detail and summary) 13 50
T-SQL Default value in Select? 5 26
error in my cursor 5 29
This article describes some very basic things about SQL Server filegroups.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

776 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