CODEWITHSUNDEEP
mysqlgroup-bygroup-concat
Varun Paprunia
Varun Paprunia

Reputation: 33

how to use group_concat without group by

I've a table like below

    +------------+----------------+--------------------------------+    
    | date       | file_fields_id | value                          |  
    +------------+----------------+--------------------------------+  
    | 2015-12-03 |            124 | AAA                            |  
    | 2015-12-03 |            125 | BBB                            |  
    | 2015-12-03 |            126 | CCC                            |  
    | 2015-12-03 |            134 | T                              |  
    | 2015-12-03 |            135 | 22222333                       |  
    | 2015-12-03 |            136 | 5216                           |  
    | 2015-12-03 |            138 | D7989878978979892              |  
    | 2015-12-03 |            139 |                                |  
    | 2015-12-03 |            143 |                                |  
    | 2015-12-03 |            124 | AAA                            |   
    | 2015-12-03 |            125 | SDOGX                          |  
    | 2015-12-03 |            126 | CUSNETDOGSUSDEW--P-US-L--      |  
    | 2015-12-03 |            134 | MO                             |  
    | 2015-12-03 |            135 | 33333222                       |  
    | 2015-12-03 |            136 | 5128                           |  
    | 2015-12-03 |            138 | D54565210545542000             |  
    | 2015-12-03 |            139 |                                |  
    | 2015-12-03 |            143 |                                |

I want this data in two rows for file_fields_id from 124 - 143 in each row. I'm using group_concat but as the table doesn't have any other unique identifier I'm not able to use group by.

The result should look something like 

    | 2015-12-03 | ([124#AAA], [125#BBB], [126#CCC]... [138#D7989878978979892],[143#])|        
    | 2015-12-03 | ([124#AAA], [125#BBB], [126#CCC]... [138#D7989878978979892],[143#])|

Upvotes: 0

Views: 5018

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

What about this. use variable to decide which will be first

Sql Fiddle Demo

SELECT row_id, 
       date,
       group_concat( value ORDER BY value SEPARATOR ', ')
FROM 
    (SELECT 
        @row_number:=CASE
                        WHEN @customer_no = file_fields_id THEN @row_number + 1
                        ELSE 1
                     END AS row_id,
        @customer_no:= file_fields_id as file_fields_id,
        date,
        CONCAT('[',file_fields_id,'#',value,']') as value
    FROM
        Table1
     ORDER BY file_fields_id
     ) T
GROUP BY row_id, date
ORDER BY row_id;

OUTPUT

| row_id |                       date |                                                                                      group_concat( value ORDER BY value SEPARATOR ', ') |
|--------|----------------------------|-----------------------------------------------------------------------------------------------------------------------------------------|
|      1 | December, 03 2015 00:00:00 | [124#AAA], [125#SDOGX], [126#CUSNETDOGSUSDEW--P-US-L--], [134#MO], [135#22222333], [136#5128], [138#D54565210545542000], [139#], [143#] |
|      2 | December, 03 2015 00:00:00 |                           [124#AAA], [125#BBB], [126#CCC], [134#T], [135#33333222], [136#5216], [138#D7989878978979892], [139#], [143#] |

Maybe you need check 

ORDER BY file_fields_id
to 
ORDER BY file_fields_id, value

Upvotes: 1

Related Questions