Help with ordering a group_concat query in mysql

[inogen]

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.

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

 

[Mr Jack]

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?

 

[inogen]

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?

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

And yes, sorry, erroneous ')' have edited. Good spot

 

[Mr Jack]

Hmm. Okay, seems in MySQL you have to put the separator after the order by. Try group_concat(items.item_name order by lookup_table.item_order separator ', ') ?

 

[inogen]

Hmm. Okay, seems in MySQL you have to put the separator after the order by. Try group_concat(items.item_name order by lookup_table.item_order separator ', ') ?

I think that's cracked it!

Thank you very much for help