Group By Order By returning wrong column - mysql

Question

I have this table:

msg_id	msg	from_user	to_user
1	Hello!	16	77
2	Wassup?	16	77
3	Hey there!	77	16
4	Hola!	7	77

I want to group these messages in descending order while taking 77 as current user, like this:

msg_id	msg	other_user
4	Hola!	7
3	Hey there!	16

This is what I have tried:

SELECT (CASE WHEN from_user = 77 THEN to_user ELSE from_user END) AS other_user, 
       MAX(msg_id) as id, 
       msg 
FROM chat_schema 
WHERE 77 IN (from_user, to_user) 
GROUP BY other_user 
ORDER BY id DESC;

This is the result of following query:

id	msg	other_user
4	Hola!	7
3	Hello!	16

For some reason, the ids are correct but the message does not match up with that id (id 3 message is 'Hey there' but it's returning 'Hello!' which is id 1). It is fetching the first message of each group instead of the message from that particular id. How to fix this?

Answer 1

For MySql 8.0+ use ROW_NUMBER() window function:

WITH cte AS (
  SELECT *, 
         ROW_NUMBER() OVER (
           PARTITION BY CASE WHEN from_user = 77 THEN to_user ELSE from_user END 
           ORDER BY msg_id DESC
         ) rn
  FROM chat_schema
  WHERE 77 IN (from_user, to_user)
)
SELECT msg_id, msg, from_user, to_user
FROM cte 
WHERE rn = 1
ORDER BY msg_id DESC;

For previous versions use NOT EXISTS:

SELECT cs1.*
FROM chat_schema cs1
WHERE 77 IN (cs1.from_user, cs1.to_user)
  AND NOT EXISTS (
            SELECT *
            FROM chat_schema cs2
            WHERE (cs2.from_user, cs2.to_user) IN ((cs1.from_user, cs1.to_user), (cs1.to_user, cs1.from_user))
              AND cs2.msg_id > cs1.msg_id
          )
ORDER BY cs1.msg_id DESC;

See the demo.

Answer 2

If you are trying to get the last message (sent, received) by a specific user, you may try the following:

Select C.msg_id, C.msg, 
       Case
           When C.from_user=77 Then C.to_user
           Else C.from_user
       End as other_user, D.other_user_type
From
    (
      Select Max(msg_id) as mid, 'R' as other_user_type From chat_schema
      Where from_user=77
      Union All
      Select Max(msg_id), 'S' From chat_schema
      Where to_user=77
     ) D
Join chat_schema C
On D.mid = C.msg_id

I added an extra column other_user_Type to specify the other_user type (sender or receiver), you may remove it from the result set.

See a demo from db_fiddle.

Update to select the last conversations a user had:

Select C.msg_id, C.msg, C.from_user, C.to_user
From
    ( 
      Select u1, u2, Max(m) mid 
      From
      ( 
        Select from_user u1, to_user u2, msg_id m From chat_schema
        Where from_user = 77
        Union All
        Select to_user, from_user, msg_id From chat_schema
        Where to_user = 77
      ) D
     Group By u1, u2
    ) F
Join chat_schema C
On F.mid = C.msg_id

See a demo from db-fiddle.