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
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.