I have query which looks like:
SELECT * FROM
( SELECT DISTINCT CASE
WHEN user1_id = 1
THEN user2_id
ELSE user1_id
END userID,conversationId
FROM conversations
WHERE 1 IN (user2_id,user1_id))dt
INNER JOIN users on dt.userID = users.id
It returns conversationId and information about user from users table. I would like to also add the last message (the one with biggest messageId) from message table on base of conversationId. The last thing would be to sort all the results by messageId
I tried to use another INNER JOIN which looked like :
INNER JOIN message on dt.conversationId = message.conversationId
Its adding messages to the result but I would like to get only the last one (the one with highest messageId as mentioned). I guess I would have to implement MAX somehow but I dont have idea how. The same thing with sorting all result by messageId so results with the biggest messageId would be first.
Thanks for all suggestions.
2
Answers
So the solution for eveything was following query
You can get the highest messageId for the conversation in a corelated subquery and use it for your join condition: