How To Get The Latest Message In Each Conversation Of A Certain User In Sql?

I need to write a query that returns the latest message in a conversation between two users. I've included the schema and my (failed) attempts in this fiddle: http://sqlfiddle.com/

Solution 1:

... the latest message in a conversation between two users.

Assuming the users with ID 1 and 3, like you did in the fiddle, we are interested in the message with the latest created_at and (sender_id, receiver_id) being (1,3) or (3,1).

You can use ad-hoc row types to make the syntax short:

SELECT*FROM   messages 
WHERE  (sender_id, receiver_id) IN ((1,3), (3,1))
ORDERBY created_at DESC
LIMIT  1;

Or explicitly (and slightly faster, also easier to use with indexes):

SELECT*FROM   messages 
WHERE (sender_id =1AND receiver_id =3OR
       sender_id =3AND receiver_id =1)
ORDERBY created_at DESC
LIMIT  1;

For all conversations of a user

Added solution as per request in comment.

SELECTDISTINCTON (user_id) *FROM (
   SELECT'out'AS type, id, receiver_id AS user_id, body, created_at
   FROM   messages 
   WHERE  sender_id =1UNIONALLSELECT'in'AS type, id, sender_id AS user_id, body, created_at
   FROM   messages 
   WHERE  receiver_id =1
   ) sub
ORDERBY user_id, created_at DESC;

The approach here is to fold foreign sender / receiver into one column to simplify the extraction of the last row.

Detailed explanation for DISTINCT ON in this related answer: Select first row in each GROUP BY group?

-> Updated SQLfiddle.

Also consider the improved and simplified test case in the fiddle.

Solution 2:

This provides the latest message between two users, regardless of message direction:

SELECTDistinct mes.ID, sendu.Username AS Sender,
  recu.Username as Receiver, Body, maxSent as TimeSent

  FROM messages mes

  INNERJOIN 

  (
    SELECTOne, Two, MAX(CREATED_AT) maxSent
    FROM
    (
      SELECT'Sender'as type, Sender_ID ASOne, receiver_id as Two,created_At
      FROM messages 

      UNIONALLSELECT'Receiver'as type, receiver_id ASOne, Sender_ID as Two ,created_At
      FROM messages 
    ) a

    GroupByOne,Two
  ) b

  ON mes.created_at = b.maxSent

  INNERJOIN users sendu
  ON sendu.ID = mes.Sender_ID

  INNERJOIN users recu
  ON recu.ID = mes.Receiver_ID

It does not separate 'conversations', but there is nothing to signify that. Perhaps if you also include a message header or title field this will be possible.

Solution 3:

SELECT*FROM messages 
WHERE (sender_id =1AND receiver_id =2) 
   OR (sender_id =2AND receiver_id =1) 
ORDERBY created_at DESC
LIMIT 1;

Solution 4:

Try this and see

SELECT*FROM    (SELECT* ,ROW_NUMBER() OVER(ORDERBY created_at DESC) as RowID
FROM   messages 
WHERE  (sender_id, receiver_id) IN ((1,3), (3,1))
    ) sub
WHERE   RowID =1

Solution 5:

use this --> select * from messages order by created_at desc limit 1