SQL nested query issue

Question

I have a database with the Table MESSAGE and it contains all of my messages. I need to find all the last conversation messages.

The table contains the following fields: Id (int) From (int) To (int) Date (date) Message (varchar)

I need to find a query that returns me all of the last messages. For example:

1 -> 3 : This is a first message; yesterday
3 -> 1 : This is the last one; today
1 -> 2 : Another message with 1 and 2; some time
3 -> 5 : Some message i don't need; some time

I need to find:

"3 -> 1 : This is the last one; today"
"1 -> 2 : Another message with 1 and 2; some time"

I hope it's clear what I mean... I can already find the users i have a conversation with, with this query:

in this example the user has Id = 47

select distinct m.To from MESSAGE m Where m.From = 47 union select distinct m2.from From MESSAGE m2 where m2.To = 47

Thanks!

Answer 1

I think this will do what you want, assuming that id can be used to define "last message":

select m.*
from message m join
     (select least(from, to) as p1, greatest(from, to) as p2, max(id) as maxid
      from message m
      group by least(from, to), greatest(from, to)
     ) mmax
     on m.id = mmax.maxid

This uses the id to find the last record in the conversation. It then joins back to get the message.

Answer 2

Man, this is really crude and looks pretty ugly, but I think this is a decent starting point...get the from and to users into a "virtualized" single table, get the max message dates for either/both, then max those per userid, and join with the original message table. That's at least the hope! :) Mind you the "from" value is almost certainly a reserved SQL keyword, so in reality it would need to be fromID or something like that, but at any rate...with that caveat...

*EDIT: Tested previous example, which was not quite right, but this one works per SQLFiddle at http://sqlfiddle.com/#!3/3f586/2

select distinct fromid, toid, message
  from messages
  join (select distinct max(msgDate) msgdate, userid from
                    (select max(messageDate) msgdate, fromID userid
                       from messages
                      group by fromID
                      union 
                     select max(messageDate), toID userid
                       from messages
                      group by toID) as j group by userid ) as x
   on (messages.fromID=x.userid
       or messages.toID=x.userid)
  and messages.messageDate=x.msgdate