CODEWITHSUNDEEP
mysqlsql
janosdupai
janosdupai

Reputation: 597

MYSQL query: get the latest date with grouped by data

I have a table for a chat.

message I senderID I recipentID I date

I want to get a query with the latest messages group by the conversation. (For example if the 'senderID = 1 and the recipentID = 2' that is the same conversation as 'senderID = 2 and the recipentID = 1')

Upvotes: 1

Views: 240

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • You can identify a specific conversation between two people as a concatenated string of "LEAST id - GREATEST id"
  • Use a grouping on the "conversation" to find Max date (last message date) for each conversation.
  • Use this as a Derived table resultset, and Join to the main table (using ON (explicit) and WHERE (kind of implicit join))

Try the following query: 

SELECT 
  CONCAT(LEAST(t1.senderID, t1.recipentID), 
         '-', 
         GREATEST(t1.senderID, t1.recipentID)) AS t1_conversation, 
  t1.*
FROM chat_table AS t1 
JOIN ( 
      SELECT CONCAT(LEAST(t2.senderID, t2.recipentID), '-', 
                    GREATEST(t2.senderID, t2.recipentID)) AS conversation, 
             MAX(t2.date) as last_message_date 
      FROM chat_table AS t2 
      GROUP BY t2.conversation
     ) AS t3 ON t3.max_date = t1.date 
WHERE t1_conversation = t3.conversation

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use in. MySQL supports tuples with in, so here is one method:

select c.*
from chats c
where (least(senderID, recipentID), greatest(senderID, recipentID), date) in
       (select least(senderID, recipentID), greatest(senderID, recipentID), max(date)
        from chats c
        group by least(senderID, recipentID), greatest(senderID, recipentID)
       );

Upvotes: 2

Related Questions