Can1 Can1 - 5 months ago 18
SQL Question

Get Last Message from Each Conversation sql

I have a sql table which include conversation between users. I need to retrieve the last message from every conversation in order to preview it.

id | sender | receiver | message | date
1 | 1 | 2 | Hello | 2015-12-08 20:00
2 | 2 | 1 | Hey | 2015-12-08 20:10
3 | 2 | 1 | You there? | 2015-12-08 21:00
4 | 1 | 3 | Yes | 2015-12-08 21:15
5 | 4 | 1 | Hey buddy | 2015-12-08 22:00


I know many similar question on site but i couldn't fix this.

Answer

Just to define what is a conversation in your table is a pain, i suppose a conversation is all the rows where

(sender=@senderId && receiver=@receiverId) || (sender=@receiverId && receiver=@senderId)

Group by this concept, i don't even want to think it

For me you are missing a concept, the "conversation"

If you have a table conversation like this

ConversationId |  Users1  |  User2

And Message like

Id | ConversationId | UserSendingId | Message | Date

Now you can Group by ConversationId and take the last message like

SELECT *  <-- avoid * better use all row names
FROM Message 
Where id in (
 select max(id) from message group by ConversationId
)

The representation of the conversation table is just a fast approach you can do a better solution with a relation from 1 to many of conversation and users in conversation to avoid modified conversation table when you want to have more than 2 users per conversation.