Help with ordering a group_concat query in mysql

inogen

inogen

inogen

Soldato
Joined
19 Jul 2009
Posts
7,250
I'm struggling with this... I thought it would be relatively simple...

I have a DB that has items and groups joined through a lookup table. I need to be able to produce results from it with the item names the first column and the groups that are in those items are in, in a comma delineated list in the second. What complicates it is making that delineated list appear in the order specified in the lookup table for each group.

Code: 
SELECT
    group.group_name
    group_concat(items.item_name  SEPARATOR ', ' )
FROM
    (groups
    JOIN (items
    JOIN lookup_table ON (items.item_id = lookup_table.item_id ON (groups.group_id = lookup_table.group_id))
GROUP BY
    groups.group_name
ORDER BY
    lookup_table.item_order

This works but the ORDER BY clause isn't working (and I don't think it's in the right place!) so the groups just appear in the sequential order that they're in the group table in.

Any help much appreciated :)
 
Last edited:
group_concat() can have its own order by statement. Have you tried group_concat(items.item_name separator ', ' order by lookup_table.item_order) ?

Also, why is there two close brackets at the end of your group_concat?
 
Mr Jack said:
group_concat() can have its own order by statement. Have you tried group_concat(items.item_name separator ', ' order by lookup_table.item_order) ?

Also, why is there two close brackets at the end of your group_concat?
Click to expand...

Yeah, I've tried that. I get a 1064 syntax error.

And yes, sorry, erroneous ')' have edited. Good spot :)
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom