Solved

order by within group concat

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL anywhere 11 databases 1 68
insert image with its metadata into SQL server Database 3 90
myqsl update statement on phpMyAdmin 8 22
MySQL Error Code 2 7
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now