SQL - Get last message from each conversation

Question

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:

Data I want using query:

And no pictures, thanks. Instead, consider following this simple two-step course of action: 1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry
Sorry if I forget to mention that it's a question and I want you guys to help me find a way to retrieve last messages of each user from messages table. I used SELECT and GROUP BY statement but it doesn't help me and the results are not what I expect it to be. — user1873111
@Strawberry: I used "SELECT * FROM messages GROUP BY user" but its not working for me. I want last messages of each user from 'messages' table. — user1873111

Sami Sami · Accepted Answer · 2016-07-26T20:30:04

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

SQL - Get last message from each conversation

Explanation of columns:

1 Answers