How to get last message of all users who did chat with UserID 1. I want to apply group by on two feilds. The Database is like:
MessageID Message MessageCreatorUserID MessageRecieverUserID CreatedAt 1 HI 4 1 2 Hello 1 3 3 Bye 1 3 4 Thanks 1 2 5 hii 1 3
This is table and My question is:
UserID is stored in MessageCreatorUserID or MessageRecieverUserID
I want to get last message of all users, who did the chat with UserID 1.
Well, you can use window functions:
select t.* from (select t.*, row_number() over (partition by (case when MessageCreatorUserId = 1 then MessageReceiverUserId else MessageCreatorUserId end) order by messageId desc ) as seqnum from t where 1 in (MessageCreatorUserId, MessageReceiverUserId) ) t where seqnum = 1;