-2

my message table

message_id | message_body_content | sender_user_id | receiver_user_id
1          | hello world          | 1000           | 1200
2          | hello world 2        | 1001           | 1200
3          | hello world again    | 1000           | 1200
4          | xyz                  | 1001           | 1200
5          | abc                  | 1200           | 1999

I want to group by my message table by sender_user_id unique and by last message_id

so output should be like;

message_id | message_body_content | sender_user_id | receiver_user_id
3          | hello world again    | 1000           | 1200
4          | xyz                  | 1001           | 1200

Because latest message_id for sender_user_id IS 3. This is in theory.

When I try

SELECT * FROM message WHERE receiver_user_id = 1200 
GROUP BY sender_user_id
ORDER BY message_id DESC

It did not work. Because MySQL grouping first by message_id (min message_id value)

then sorting.

How can I get unique sender message by max message_id

hakki
  • 6,181
  • 6
  • 62
  • 106

3 Answers3

1

I don't think you want to "group". You want to filter. So, I think you want:

SELECT m.*
FROM message m
WHERE m.receiver_user_id = 1200 AND
      m.message_id = (SELECT MAX(m2.message_d)
                      FROM message m2
                      WHERE m2.receiver_user_id = m.receiver_user_id AND
                            m2.sender_user_id = m.sender_user_id 
                     )
ORDER BY m.message_id DESC;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1
select message_id, message_body_content, sender_user_id, receiver_user_id
from message t
where t.message_id = (select max(t1.message_id)
                      from message t1
                      where t1.sender_user_id = t.sender_user_id
                     )
order by message_id;
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

you can use sub-query

select m.* from message m
where (m.message_id,m.sender_user_id) in(
SELECT max(message_id) as message_id ,sender_user_id
 FROM message  
GROUP BY sender_user_id
 ) and m.receiver_user_id = 1200
ORDER BY m.message_id DESC
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63