Solved

order by within group concat

Posted on 2011-02-22
4
326 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:
Kent Olsen 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

617 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