SELECT the last message of conversation

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.

CodePudding user response：

You can get the highest messageId for the conversation in a corelated subquery and use it for your join condition:

INNER JOIN message m
  on  m.conversationId = dt.conversationId
  and m.messageId = (
    SELECT MAX(m1.messageId)
    FROM message m1
    WHERE m1.conversationId = dt.conversationId
  )

CodePudding user response：

So the solution for eveything was following query

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 
INNER JOIN message m on  m.conversationId = dt.conversationId and m.messageId = (SELECT MAX(m1.messageId) 
FROM message m1 WHERE m1.conversationId = dt.conversationId) 
ORDER by m.messageId DESC