Group By Order By returning wrong column - mysql

user_13921452 0
Viewed 298

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?

chat mysql sql
5 Answers

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.

user_104988280

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.

user_127059120

By all the questions and answers mentioned, I assume there might be many users interacting with User 77. I have presumed you want the last conversation(either sent or received) with User 77:

Please do try the following query and let me know how it goes with you:

SELECT 
maxMsgId,
msg,
(CASE WHEN from_user=77 THEN to_user ELSE from_user END) AS otherUser
FROM (
SELECT MAX(cs.`msg_id`) AS maxMsgId
FROM 
`chat_schema` cs 
WHERE cs.`from_user`=77 OR cs.`to_user`=77
 GROUP BY 
 LEAST(cs.`from_user`, cs.`to_user`), 
 GREATEST(cs.`from_user`, cs.`to_user`)) 
 primarySelect
 JOIN `chat_schema` c ON primarySelect.maxMsgId=c.`msg_id`
 ORDER BY c.`msg_id` DESC
user_80357590

i have find max ids with group by than use in where condition

select msg_id, msg,
(CASE WHEN from_user = 77 THEN to_user ELSE from_user END) as other_user
from chat_schema 
where msg_id in (select 
Max(msg_id) as id
from chat_schema
where 77 IN (from_user, to_user)
group by (CASE WHEN from_user = 77 THEN to_user ELSE from_user END))
order by msg_id desc
user_85097280

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
Admin1
Related