SOQ#46673880, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

Drop table if exists SO46673880_ChatMsgs;
Drop table if exists SO46673880_Users;

Create table SO46673880_ChatMsgs(
    ChatID int,
    FKFromUID int,
    FKToUID int,
    Message varchar(20));

Create table SO46673880_Users(
    userID int,
    fullname varchar(20));
    
Insert into SO46673880_ChatMsgs values 
(1 ,         3 ,        2 ,    'Hello1'),
(2 ,         2 ,        1 ,    'Hello2'),
(3 ,         2 ,        3 ,    'Hello3'),
(4 ,         3 ,        2 ,    'Hi1'),
(5 ,         2 ,        3 ,    'Hi2'),
(6 ,         3 ,        2 ,    'Hi3');

INSERT INTO SO46673880_Users values 
      (1,    'User1'),
      (2,    'User2'), 
      (3,    'User3');

SELECT U.userID, U.FullName, Cm.Message
FROM (SELECT MAX(Z.ChatID) chatID, UID
FROM (SELECT ChatID, case when FKFROMUID = 2 then FKTOUID else FKFROMUID end as UID from SO46673880_ChatMsgs) Z
GROUP BY UID) Y
INNER JOIN SO46673880_Users U
 on U.userID = Y.UID
INNER JOIN SO46673880_ChatMsgs CM
 on Cm.ChatID = Y.ChatID 
ORDER BY Y.chatID desc;

Drop table if exists SO46673880_ChatMsgs;
Drop table if exists SO46673880_Users;


absolute service time: 0,38 sec