How to get conversations' last message in messaging system? ie. home screen of (whatsapp)
I have created this stored procedure but unable to get correct result.
SELECT
Max(msg.msgThread) as msgThread,
Max(msg.msgId) AS msgId,
Max(msg.msgDate) AS msgDate,
min(DATEDIFF(MINUTE,msgCreatedDate,GETUTCDATE())) AS mintuesago,
msgReceiverId,
msgSenderId,
max (msgmsg) as msg, --I am getting alpabatic max msg instead last msg.
usr.umName,
usr.umProfilePic
FROM messages AS msg
Inner Join usermaster AS usr ON ((msg.msgSenderId = usr.umId)
or (msg.msgReceiverId = usr.umId) )
WHERE
--usr.umId=msg.msgReceiverId
usr.umId!=3198
and (msg.msgSenderId=3198 or msg.msgReceiverId =3198)
GROUP BY usr.umName, usr.umProfilePic, msgReceiverId, msgSenderId, msgmsg
--,msgSentById
ORDER BY max(DATEDIFF(MINUTE,msgDate,GETUTCDATE()))
with this, I can get the list of users, but when both user has send messages to each other then it gives two rows with last message of both user.
While this is tough to troubleshoot without any data and being forced to infer your table structure, it looks like adding the msgThread to your grouping will prevent duplicate records from the same message thread. Selecting top 1 will ensure that you only get the most recent record (assuming your order by clause is working properly).
For future reference: If you provide sample data, explain what you're getting, and explain what you want to get, it will be a lot easier to help you.
Untested code:
SELECT top 1
msg.msgThread as msgThread,
Max(msg.msgId) AS msgId,
Max(msg.msgDate) AS msgDate,
min(DATEDIFF(MINUTE,msgCreatedDate,GETUTCDATE())) AS mintuesago,
msgReceiverId,
msgSenderId,
max (msgmsg) as msg, --I am getting alpabatic max msg instead last msg.
usr.umName,
usr.umProfilePic
FROM messages AS msg
Inner Join usermaster AS usr ON ((msg.msgSenderId = usr.umId)
or (msg.msgReceiverId = usr.umId) )
WHERE
--usr.umId=msg.msgReceiverId
usr.umId!=3198
and (msg.msgSenderId=3198 or msg.msgReceiverId =3198)
GROUP BY msgThread, usr.umName, usr.umProfilePic, msgReceiverId, msgSenderId, msgmsg
--,msgSentById
ORDER BY max(DATEDIFF(MINUTE,msgDate,GETUTCDATE()))