How to get the latest message in each conversation of a certain user in SQL?

Question

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/#!15/322c3/11

I've been working around the problem for some time now but every time I run any of my ugly queries a sweet little kitten dies.

Any help would be much appreciated. Do it for the kittens.

+1 for the working fiddle. – Erwin Brandstetter Dec 31 '13 at 11:45 — Erwin Brandstetter, Dec 31 '13 at 11:45

Erwin Brandstetter · Accepted Answer · 2022-04-20T23:05:36.410

7

... 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))
ORDER  BY created_at DESC
LIMIT  1;

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

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

For all conversations of a user

Added solution as per request in comment.

SELECT DISTINCT ON (user_id) *
FROM (
   SELECT 'out' AS type, id, receiver_id AS user_id, body, created_at
   FROM   messages 
   WHERE  sender_id = 1
  
   UNION  ALL
   SELECT 'in' AS type, id, sender_id AS user_id, body, created_at
   FROM   messages 
   WHERE  receiver_id = 1
   ) sub
ORDER  BY 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?

sqlfiddle - with improved and simplified test case

edited Apr 20 '22 at 23:05

answered Dec 31 '13 at 11:38

Erwin Brandstetter

605,456
145
1,078
1,228

I love this solution. Looks very elegent. But what if I want to list all the latests messages for ALL the conversations of a specific user? That is each row represents a different conversation of a queried user. – Wittek Dec 31 '13 at 11:51
Are you sure the two versions of your query will generate different execution plans? I would expect that the optimizer can see that they are identical and generate the same plan (including index usage). At least with the tiny data set from the SQLFiddle the plans are 100% identical. – Dec 31 '13 at 12:06
@Wittek: I added a solution for that. – Erwin Brandstetter Dec 31 '13 at 12:18
@Erwin Isn't that just my solution? – user3056839 Dec 31 '13 at 12:21
@a_horse_with_no_name: I have seen the row-wise comparison being a bit slower in the past. But maybe this has been remedied since? Maybe the OP can run a quick test with each of the two queries using `EXPLAIN ANALYZE` on his big table (with indexes?) and report back? – Erwin Brandstetter Dec 31 '13 at 12:21
@user3056839: Simililar basic approach, yes. +1 for you. I didn't check after Witteks follow up question. `DISTINCT ON` is superior to get whole rows, though. And the UNION ALL approach is [not exactly prior art](http://stackoverflow.com/questions/15186763/how-do-i-get-unique-user-engagements/15189159#15189159). – Erwin Brandstetter Dec 31 '13 at 12:30
distinct on(userid) not working in sqlserver, any solution? – Mohammad Reza Mrg May 05 '20 at 21:58

score 3 · Answer 2 · answered Dec 31 '13 at 11:51

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

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

  FROM messages mes

  INNER JOIN 

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

      UNION ALL

      SELECT 'Receiver' as type, receiver_id AS One, Sender_ID as Two ,created_At
      FROM messages 
    ) a

    Group By One,Two
  ) b

  ON mes.created_at = b.maxSent

  INNER JOIN users sendu
  ON sendu.ID = mes.Sender_ID

  INNER JOIN 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.

gbejic · Answer 3 · 2014-01-02T08:27:20.647

0

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

edited Jan 02 '14 at 08:27

answered Dec 31 '13 at 11:24

gbejic

302
1
5

Combines all conversations with all users instead of just those with a particular user. – Erwin Brandstetter Dec 31 '13 at 13:04

SAT · Answer 4 · 2015-08-14T07:34:12.137

-1

Try this and see

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

edited Aug 14 '15 at 07:34

answered Dec 31 '13 at 11:16

SAT

647
1
12
23

2

This only gets the latest message, not, as the question says, *the latest message in a conversation between two users*. There is nothing in the where clause, you have not selected any columns, and Postrgesql uses `LIMIT` not `TOP`. – GarethD Dec 31 '13 at 11:23
1

plus: it should at least be `top 1`, not `top1` – Dec 31 '13 at 11:32

score -2 · Answer 5 · answered Dec 31 '13 at 11:23

-2

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

answered Dec 31 '13 at 11:23

HarshSharma

630
3
9
34

Doesn't answer question at all. – Erwin Brandstetter Dec 31 '13 at 13:05

How to get the latest message in each conversation of a certain user in SQL?

5 Answers5

For all conversations of a user

Linked