Group By Order By returning wrong column

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?

CodePudding user response：

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.

CodePudding user response：

The query is quite identical in (CASE/IF) but semplified. Perhaps your query is a bit weird. Try to see without LIMIT 2 what happens

SELECT IF(from_user=77, to_user, from_user) AS other_user, id, msg 
FROM chat_schema
ORDER BY id DESC
LIMIT 2