mysql - Why order by not working? -

i have following query:

select distinct case when msg_from = 35      msg_to else msg_from  end msg_from_result , msg_id,msg_from,msg_to,msg_content,msg_status,msg_created_at,     (select user_name users user_id = msg_from_result) user_name     messages      35 in (msg_from , msg_to)  group msg_from_result      order msg_id desc 

this should retrieve conversations last message belonging it. conversations retrieved, problem here can't use order by last msg setting order msg_id desc.

what's wrong this? there way it?

i think you're trying do, here's how i'd write (if i'm understanding correctly):

select users.user_name, msg_id, msg_from, msg_to, msg_content, msg_status, msg_created_at ( select if(msg_from = 35,msg_to,msg_from) msg_from_result , max(msg_id) last_message messages  35 in (msg_from,msg_to)  group msg_from_result,last_message ) der_table -- creating table of conversations , last message ids join users on user_id=der_table.msg_from_result join messages on messages.msg_id=der_table.last_message order msg_id desc 

you need last message id before can message's content etc.