user1873111 user1873111 - 3 months ago 10
SQL Question

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


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

The perfect answer you need is

select messages.* from messages
(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

Or With Subquery

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