1

I have this SQL table Messages where the relevant entries are sender, recipient, timestamp and text. So for instance some examples would be

sender     recipient    text             timestamp
user1      user2        hey              1
user2      user1        hello back       2
user1      user3        hey other dude   3

And in order to populate a view like in iMessage for instance; I need an SQL query that for a given user gets the most recent message in each conversation that user is having. So currently what I have is 

SELECT * 
FROM Messages m 
WHERE m.timestamp IN (SELECT MAX(m2.timestamp) 
               FROM Messages m2 
               WHERE m.sender = :user OR m.recipient = :user 
               GROUP BY sender, recipient)

But unfortunately this query returns all 3 of the messages above because the nested select groups the first two separately even though they're both in the same conversation. Is there a simple way to express what I really want in SQL, preferably without creating a Conversations table and doing some sort of joiny business?

Inquisitive Idiot
  • 412
  • 4
  • 19
  • 1
    simple query? no not really. conversations table would be a good idea. otherwise [see my answer here](http://stackoverflow.com/questions/29799891/finding-latest-message-from-table-grouped-by-user-in-mysql/29800974#29800974). it's basically the same question – pala_ May 26 '15 at 04:56
  • What is the `id`? I think it is your PK of Messages and you forgot it in your table structure ;). – shA.t May 26 '15 at 07:01
  • This is MySQL, right? – Strawberry May 26 '15 at 07:29
  • Do you still need help with this one? – pala_ Jun 02 '15 at 03:21
  • Well, I have a workaround where I just do the select as in my original query and filter down to the subset of results I want in Java. It would still be cool to do it all in SQL, but that's not looking possible. – Inquisitive Idiot Jun 02 '15 at 03:27

3 Answers3

0

Here in outer query you are trying to compare "m.id" with inner query instead of using "m.id" you should use "m.timestamp"

SELECT * 
FROM Messages m 
WHERE m.timestamp IN (SELECT max(m2.timestamp)
                     FROM Messages m2 
                     WHERE m.sender = :user 
                        OR m.recipient = :user)

Anirudh Jadhav
  • 967
  • 1
  • 9
  • 23
  • 1
    even still it's not going to give decent results because messages between two different people can be sent at the same time. – pala_ May 26 '15 at 05:35
  • ok pala_ then i have edited the following query you can test it down and let me know – Anirudh Jadhav May 26 '15 at 06:09
  • you can't do that. what is `(select id from (select max(.....` supposed to accomplish? other than an unknown column error? as i posted against the question, [this answer](http://stackoverflow.com/questions/29799891/finding-latest-message-from-table-grouped-by-user-in-mysql/29800974#29800974) shows a working method of doing this – pala_ May 26 '15 at 06:13
  • Sorry pala_ can you now check it and test it down – Anirudh Jadhav May 26 '15 at 06:53
  • 2
    group by doesn't work like that - you can't just throw in a value you aren't grouping by or aggregating, and expect to always get the right value.. you're barking up the wrong tree – pala_ May 26 '15 at 06:58
  • ok pala_ i have removed it and it's working for me any more edits you think should have – Anirudh Jadhav May 26 '15 at 07:05
  • My last comment is still valid. Your very inner query has an indeterminate result for m2.id. consider that you are implicitly grouping on the entire table. timestamp is determined because you want the `max` - that is reproducable. how does mysql choose which value to use for id? answer: whichever value it feels like choosing. it may change from architecture to architecture, storage engine to storage engine and version to version. it's unreliable. [you should read this](https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html) – pala_ May 26 '15 at 07:20
0

Your method is largely unworkable. Right now you are checking for WHERE m.id IN (SELECT MAX(m2.timestamp) which is obviously not going to work.

Assuming it's a typo, and you meant to check WHERE m.timestamp IN ... it's still not going to be reliable, because more than one message from different conversations may have the same timestamp.

Instead, let's abandon that method, because it's fraught with difficulties, and instead let's just perform an anti-join.

select m.*
  from messages m 
     left join messages m2 
       on ((m.sender = m2.sender and m.recipient = m2.recipient) 
           or (m.sender = m2.recipient and m.recipient = m2.sender)) 
         and m.timestamp < m2.timestamp 
  where m2.sender is null

demo here

by joining on two conditions, we essentially join on 'conversation', whether either the sender and receiver are the same, or the sender of one is the receiver of the other. our final predicate there is a time range - when there doesn't exist a message with a higher timestamp, the outer join will return nulls, so we filter for those in the where clause (this is the anti-join)

pala_
  • 8,901
  • 1
  • 15
  • 32
-1

If you want to find last message of a user try this:

SELECT *
FROM `Messages` 
WHERE `recipient` = :user OR `sender` = :user
ORDER BY `timestamp` DESC
LIMIT 1

And if you want to find last message between two users try this:

SELECT *
FROM `Messages` 
WHERE
    :user1 + :user2 = 
    CASE WHEN `recipient` = :user1 THEN `recipient`
         WHEN `sender` = :user1 THEN `sender`
         ELSE NULL
    END +
    CASE WHEN `recipient` = :user2 THEN `recipient`
         WHEN `sender` = :user2 THEN `sender`
         ELSE NULL
    END 
ORDER BY `timestamp` DESC
LIMIT 1
shA.t
  • 16,580
  • 5
  • 54
  • 111