sql - How can I recover the users with whom a user chatted recently from a MySQL db? -

i ask in query came across.

basically, have 3 tables:

1. 'users', stores data of user in db, fields are:

   • usr_id -> id of user, primary key;
   • usr_profile_photo_id, foreign key references profile photo filename in 'files' table names of files of user stored (in case foreign key references profile photo filename);
   • usr_name -> name of user;
   • usr_surname -> surname;

2. 'files', stores files of user, fields are:

   • fil_usr_id -> foreign key references user in 'users' table;
   • fil_uid -> referenced file id (this field referenced 'usr_profile_photo_id');
   • fil_name -> name of file (e.g. user154241profile.png, user163451profile.png, etc.);

3. 'chat', stores chat messages of users, fields are:

   • id -> primary key, identifies in univocal way chat message;
   • chat_from -> foreign key references id of user wrote chat message;
   • chat_fromname -> name surname (e.g. "john smith", "mary smith", etc.) of user wrote chat message;
   • chat_to -> foreign key references id of user whom chat message adressed;
   • chat_toname -> complete name of 'to' user (like chat_fromname);
   • chat_sent -> date of message (date mysql type);

what need recover users whom user has chatted, , so:

select u.usr_id,        c.chat_fromname,        f.fil_uid users u chat c,      files f c.chat_from = u.usr_id   , f.fil_uid = u.usr_profile_photo_id   , u.usr_id in     (      select g.chat_from      chat g      g.chat_to = 1      union select d.chat_to      chat d      d.chat_from = 1     ) group u.usr_id,          c.chat_fromname,          f.fil_uid order c.chat_sent desc; 

the problem query doesn't seem behave expect, cause returns users user chatted recently, not ordered date of chat message sent (chat_sent column), seems mysql ignoring order clause, or maybe wrong in something?

what should retrieve users ordered in correct way?

edit: possible better solution:

select chat_from, chat_fromname chat c c.to = 1 group chat_from, chat_fromname order max(c.chat_sent) desc limit 10; 

here recover last 10 users wrote message user (so user receiver).

then like:

select u.usr_id, f.fil_uid users u, files f f.fil_uid = u.usr_profile_photo_id , ( -- dinamically built based on previous result. u.usr_id = 1 or  u.usr_id = 2 or  u.usr_id = 3 or  u.usr_id = 4 or  u.usr_id = 5 or  u.usr_id = 6 or  u.usr_id = 7 or  u.usr_id = 8 or  u.usr_id = 9 or  u.usr_id = 10); 

but problem here recover users wrote user, hope clear... if user wrotes user 8 example (so user 8 chat_to receiver)?