Menu
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.
• 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:
717
The SQL LIKE Operator The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters.
1 Answer. ORDER BY id ASC; In the above query, we used subquery with the TOP clause that returns the table with the last 5 records sorted by ID in descending order. Again, we used to order by clause to sort the result-set of the subquery in ascending order by the ID column.
To get the last record, the following is the query. mysql> select *from getLastRecord ORDER BY id DESC LIMIT 1; The following is the output. The above output shows that we have fetched the last record, with Id 4 and Name Carol.
We can use the ORDER BY statement and LIMT clause to extract the last data. The basic idea is to sort the sort the table in descending order and then we will limit the number of rows to 1. In this way, we will get the output as the last row of the table.
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
136
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
