So i have 2 tables, users and messages
users table
uid, username
messages table
mid, senderid, receiverid, message, timestamp
The query i have at the moment is retrieving the uid, username and last timestamp of each chat conversation
SELECT DISTINCT
IF (messages.senderid = '5e9b95786a71f8.25790415', messages.receiverid, messages.senderid) as uid,
(SELECT username FROM users WHERE uid = IF (messages.senderid = '5e9b95786a71f8.25790415', messages.receiverid, messages.senderid)) as username,
MAX(messages.timestamp) as last_timestamp
FROM messages JOIN users ON users.uid = messages.senderid WHERE messages.senderid = '5e9b95786a71f8.25790415' OR messages.receiverid = '5e9b95786a71f8.25790415'
GROUP BY 1,2
which outputs below
uid | username | last_timestamp
1 | Developer | 1601826378
2 | BetaTester | 1601826301
What i need to add to this query is the message field which is based on the last_timestamp i have in the output.
How can the query be updated to include the extra message field?
Try this.
select A.uid, C.username, B.timestamp, B.message from (SELECT DISTINCT
IF (messages.senderid = '5e9b95786a71f8.25790415', messages.receiverid, messages.senderid) as uid,
MAX(messages.mid) as max_mid
FROM messages WHERE messages.senderid = '5e9b95786a71f8.25790415' OR messages.receiverid = '5e9b95786a71f8.25790415'
GROUP BY 1) A join messages B on A.max_mid = B.mid join users C on A.uid = C.uid