CODEWITHSUNDEEP
mysqlsql
A. K. M. Tariqul Islam
A. K. M. Tariqul Islam

Reputation: 2834

Select last row in chat conversation

I have a table like this:

+---+--------+----------+---------+
|id | sender | receiver | created |
+---+--------+----------+---------+
  1     4         9       3333
  2     9         4       3334
  3     4         9       3335
  4     5         4       3336
  5     4         9       3337

My query is:

SELECT *
FROM chat_messages 
WHERE sender = 4 OR receiver = 4
GROUP BY sender, receiver
ORDER BY created DESC

The result is:

+---+--------+----------+---------+
|id | sender | receiver | created |
+---+--------+----------+---------+
  5     4         9       3337
  4     5         4       3336
  2     9         4       3334

I am closer to what I look for. I am looking for this result:

+---+--------+----------+---------+
|id | sender | receiver | created |
+---+--------+----------+---------+
  5     4         9       3337
  4     5         4       3336

Meaning, I want the latest row if the conversation is of the user n, and other rows where the user n is a sender or a receiver. Thanks in advance.

Upvotes: 3

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You can use filtering in the where clause. Here is one method:

select cm.*
from chat_messages cm
where cm.created = (select max(cm2.created)
                    from chat_messages cm2
                    where (cm2.sender, cm2.receiver) in ( (cm.sender, cm.receiver), (cm.receiver, cm.sender))
                   ) and
      4 in (cm.receiver, cm.sender);

If you just want the created time and id, you might find that aggregation works better:

select least(cm.receiver, cm.sender),
       greatest(cm.receiver, cm.sender),
       max(cm.id), max(cm.created)
from chat_message cm
where 4 in (cm.receiver, cm.sender)
group by least(cm.receiver, cm.sender), greatest(cm.receiver, cm.sender);

Upvotes: 1

Related Questions