[RESOLVED] GROUP_CONCACT ORDER BY

ade234uk

I am using GROUP_CONCACT in my query to group. This works great, I have also used the ORDER BY to order the output.

However I need to ORDER BY in my own order.
At the moment I am getting mixed results.

I hope this makes some sort of sense.

djjjozsi

you can handle the concated rows in the specified fields like the other fields. Just use aliases on your values. Would you have better examples about this order problem?

ade234uk

Thanks for your help. Ordering in a specific fields would be fantastic, I'm just not to sure how you would do this. Here is my query below:-

As you can see I have ORDER BY in the GROUP_CONCAT.

The GROUP_CONCAT pulls 3 values from the db and stick them on one line. If they can be split seperately in their own fields that would amazing.

/////////////////////
SELECT a.seller_id AS Seller, from_unixtime( a.purchase_date, "%b %D %Y" ) AS Purchase_Date, b.auction_id AS Auction_ID, b.name AS Auction_Name, a.bid_amount AS Sales, GROUP_CONCAT( c.box_value
ORDER BY c.box_value SEPARATOR ' , ' ) AS Custom_Field
FROM winners a
LEFT JOIN auctions b ON b.auction_id = a.auction_id
LEFT JOIN custom_fields_data c ON c.owner_id = a.auction_id
WHERE from_unixtime( a.purchase_date ) >= str_to_date( "12/03/2009", "%d/%m/%Y" )
AND a.seller_id =13358 GROUP BY b.auction_id

djjjozsi

GROUP_CONCAT( c.box_value
ORDER BY c.box_value SEPARATOR ' , ' )

if i'm not mistaken is it the correct sntax for ordering the concated values.

If they can be split seperately in their own fields that would amazing.

You get the results in one row, in one field with a separator. CONCAT_group is for this. Better to use the normal XXX JOIN and the result will be in 3 rows. This is the cleanest version. In the concated version the entries lost their primary keys (as i mentioned in one of my post)

the coding technique is getting difficult if you need to handle comma delimitered strings.

ade234uk

I was ordering the wrong field. Like you correctly suggested. In the I changed

ORDER BY c.box_value

ORDER BY c.box_id DESC

I am now outputting the correct order. Can't believe I missed this one.

Thank you very much for your help. I would not use GROUP_CONCAT for anything else, and for what is required this does the job. This is so it can be exported via .csv in PHPMyAdmin.

djjjozsi

your welcome,

don't forget to set this thread as solved,

jjozsi.