Get biggest value of group after join

Question

I have two tables in my DB:

Conversation:

conversationid | persone | perstwo | timestamp
1              |    1    |  3      | 1431680294000
2              |    3    |  8      | 1431680407000

Message:

messageid | conversationid | senderid | receiverid | message | timestamp     |  seen
1           1                1          3            Xyz!      1431680294000    0
2           2                3          8            Hi x!     1431680405000    0
3           2                3          8            Allt bra? 1431680407000    0

Now I want to find the latest message of each conversation.

My approach right now is just to find the conversations of a user and then I try to group by the conversationid:

SELECT DISTINCT conversationid, senderid, receiverid, message, timestamp 
FROM message WHERE conversationid IN
      (SELECT conversationid FROM conversation WHERE persone = 3 OR perstwo = 3)
GROUP BY conversationid ORDER BY timestamp DESC

Unfortunately I get these results:

2   3   8   Hi x!   1431680405000
1   1   3   Xyz!    1431680294000

Even though I need Allt bra? as a result for the last conversation.

I am running the newest version available for Arch of MariaDB.

possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — jpw, May 15 '15 at 09:41

score 1 · Accepted Answer · answered May 15 '15 at 09:35

A common pattern for this type of question is to find the last item in a derived table and join with that to filter the main query. In your case the query would look something like:

select * 
from conversation c
join message m on c.conversationid = m.conversationid
join (
  select conversationid, max(timestamp) timestamp 
  from Message
  group by conversationid
) x on m.conversationid = x.conversationid
   and x.timestamp = m.timestamp
where c.persone = 3 OR c.perstwo = 3

Sample SQL Fiddle

Get biggest value of group after join

1 Answers1