CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
user1873111
user1873111

Reputation: 103

SQL - Get last message from each conversation

I've a SQL table which contains all the users and groups conversation in it. I'm able to retrieve group conversation from it easily but don't know how to retrieve last messages of each user from it.

Explanation of columns:

• message_id: Self explanatory

• group_id: Since the conversation also contains group messages, I'll use group_id to filter those messages instead of creating a new messaging table for it.

• user: Sender/Receiver (It can be both sender or receiver by defining the value on last column 'isReceived').

• message: Self explanatory

• creation: Self explanatory

• isSeen: If the message has been seen by the user (receiver).

• isError: If there was an error while sending the message.

• isReceived: To check whether the message was received or send by the default user.

Now what I really want is to retrieve last messages of all conversations no matter if its sent or received. Example, 'Sondre' (Another User) sent me a message "Hi" and now I sent him a reply/message "Hello" and when I retrieve data from messages table I want my reply to be shown instead of his. I've also uploaded photos of current data and the data using query I want:

Data of messages table:

enter image description here

Data I want using query:

enter image description here

Upvotes: 9

Views: 11298

Answers (1)

Sami
Sami

Reputation: 8419

You need to do it in two parts first get the latest messages, grouped by user Then fetch your messages against these

Simplest answer is (it would work fine if you really have message_id a unique/primary key)

select * from messages where message_id in
(select max(message_id) from messages group by user)

It will give you the latest message from each user, Yo can also group by other things instead of user

The better version you need (for better performance with scalability) is

select messages.* from messages
join
(select max(creation) maxtime,user from messages group by user) latest
on messages.creation=latest.maxtime and messages.user=latest.user;

Abvove will work even if you do not have a unique/primary key column like message_id, but if you really have it then i would recommend to use message_id instead of creation

you can see implemented SQL Fiddle Demo

Upvotes: 10

Related Questions