CODEWITHSUNDEEP
mysqlsql
Wilker Iceri
Wilker Iceri

Reputation: 141

Select last message of each conversation

I have this table:

fromJID | toJID | sentDate | body

I need the last message of last five conversation of user (toJID)

I try this, but this return the first message of conversation, and not last.

SELECT 
    body as mensagem, 
    fromJID, 
    toJID,
    sentDate 
FROM ofmessagearchive 
WHERE toJID = '1945' 
GROUP BY fromJID 
ORDER BY sentDate DESC 
LIMIT 0, 5

Example of data:

fromJID| toJID  | sentDate      | body (message)
'1945' | '2042' | 1383934233976 | '1\n'
'1945' | '2042' | 1383934234429 | '2\n'
'1945' | '2042' | 1383934234430 | '3\n'
'2042' | '1945' | 1383934237053 | '1\n'
'2042' | '1945' | 1383934237374 | '2\n3'
'2042' | '1945' | 1383934237523 | '\n'
'2042' | '1945' | 1383934242018 | '3\n'
'1945' | '1946' | 1383934364814 | '1\n'
'1945' | '1946' | 1383934365118 | '2\n'
'1945' | '1946' | 1383934365366 | '3\n'
'1946' | '1945' | 1383934367271 | '1\n'
'1946' | '1945' | 1383934367517 | '2\n'
'1946' | '1945' | 1383934367782 | '3\n'

Thank you!

Upvotes: 1

Views: 1019

Answers (1)

Kickstart
Kickstart

Reputation: 21513

Try this, using a sub query the get the last post of each conversation and joining that back against the main table:-

SELECT a.body as mensagem, 
    a.fromJID, 
    a.toJID,
    a.sentDate
FROM ofmessagearchive a
INNER JOIN
(
    SELECT fromJID, toJID, MAX(sentDate) AS MaxSentDate
    FROM ofmessagearchive
    GROUP BY fromJID, toJID
) b
ON a.fromJID = b.fromJID
AND a.toJID = b.toJID
AND a.sentDate = b.MaxSentDate
WHERE ta.oJID = '1945'
ORDER BY a.sentDate DESC
LIMIT 5

Upvotes: 3

Related Questions