0

I have a message table with fields id, time, isread, message, messagestatus, receiver and user. I want to retrieve the latest message (based on time) a user with an id of 1 sent or received to any other users.

for example if a user with id 1(sender) sends a message to user with id 2(receiver) and if user with id 2 sends user with id 1 a message. only the most current message sent between both of them should be picked

My already existing table looks like this :

enter image description here

i tried SELECT * from (SELECT MAX(time) as time ,sender,receiver from message group by sender,receiver) as f where receiver=1 or sender=1 limit 20

but it only displays the sender, receiver and time while other columns like message, id, messagestatus and read are omitted, plus it does not find the general last message sent between two users, but instead finds the most recent message sent between user1 and another user when user1 is the sender and the other user is the receiver and also finds the last message when user1 is the receiver and the other user is the sender.

Improve this question
2
  • Please share the sample input and expected output. Commented Oct 20, 2018 at 21:29
  • If you only ever want to do this for one user, and thus only ever return one record, just use ORDER BY time DESC LIMIT 1 instead of using aggregation. If you want this for multiple users in a single query, use windowed / analytic functions, and update your question to clarify that. Commented Oct 20, 2018 at 21:32

1 Answer 1

Reset to default
1

If you want the most recent messages sent or received by user "1", then use filtering, not group by:

select m.* from messages m where 1 in (m.sender, m.receiver) and m.time = (select max(m2.time) from messages m2 where (m2.sender = m.sender and m2.receiver = m.receiver) or (m2.receiver = m.sender and m2.sender = m.receiver) );

If two messages could have exactly the same timestamp, then:

select m.* from messages m where 1 in (m.sender, m.receiver) and m.id = (select id from messages m2 where (m2.sender = m.sender and m2.receiver = m.receiver) or (m2.receiver = m.sender and m2.sender = m.receiver) order by m2.time desc, m2.id desc limit 1 );
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

you are a genius sir
Just rechecked the query sir, there is a slight problem. imgur.com/a/lbsEuBy since user1 (sender) sent a message to user3 (receiver) and user3(sender this time) also sent a message to user1 (receiver this time), it should also check the most recent message in the conversation between them as a whole
@Oto-obongEshiett . . . That is what the subquery is doing.
if the subquery is supposed to get a single conversation from user one and any other user, then it is not working 100% , because it is getting me last message user1 sent and also the last message he received from a particular user (e.g if user1 sent a message to user2 and user2 sent a messge to user1) ..... please sir, try to make a dummy table, you would understand the problem better :(
Can you set up a db<>fiddle or something similar? The only issue is that you might have two messages at exactly the same time.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.