mysql group_concat order by utf8

Question

Hi everyone! I have the following problem I have 3 table (all of the are used utf8 / utf8_general_ci encoding) movies, channels, i also have 3 table movie_channels which

is just a combination of the other two with just 2 fields: movie_id,channel_id here is my channels table (code,name)

'1', 'ОРТ'
'2', 'ТК Спорт'
'3', 'ТК ТНВ'
'4', 'НТВ'
'5', 'НТВ+'
'6', 'TSN'

here is my movie_channels table (movie _id, channel_id) channel_id references code field in channels table

'19', '2'
'19', '6'
'95', '1'
'95', '4'
'96', '1'
'96', '4'
'97', '1'
'97', '4'
'98', '1'
'98', '4'
'99', '1'
'99', '4'
'100', '1'
'100', '4'

don't mind quotes on id values. they are all ints of course, not chars, it's just pasting issue

for each movie i need to display comma separated list of channels i used mysql group_concat

select t.movie_id,( select group_concat(c.name) from 
movie_channels mc
join channels c  on mc.channel_id=c.code
where mc.movie_id = t.movie_id

order by code desc )as audio_channel from movies t 

but I dont like the order of concat for movie_id #19 i need the above sql to display TSN,ТК Спорт but it keeps returning me ТК Спорт,TSN . I tried to use order by code desc with no luck ,tried order by char_length(asc) with no success any ideas ?

Answer 1

If for every movie you want to list all the channels that are showing that movie, you need something like this:

SELECT
  mc.movie_id,
  GROUP_CONCAT(c.name) AS channels
FROM movie_channels AS mc
JOIN channels AS c
ON c.code = mc.channel_id
GROUP BY mc.movie_id
ORDER BY c.code DESC;

Answer 2

You can add a ORDER BY clause inside the GROUP_CONCAT() aggregate to adjust the ordering of the grouped string.