1

I know the very very basics of MySQL but I can't seem to figure out the following problem I have.

I try to display conversations between two users in an overview, where the last message between these two users will be shown.

So this:

Message           Date           User
Lipsum            10-10-2015     Bob
Lorem             11-10-2015     John
Dolor             11-10-2015     Bob

Will become this:

Dolor             from Bob
Lorem             from John

The message sql table looks like this:

MESSAGEID         DATE           FROM_USERID          TO_USERID
Steven
  • 1,123
  • 5
  • 14
  • 31
  • I try to show only the last message per user – Steven Jan 19 '15 at 19:10
  • Similar question was posted here :http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql?rq=1 – MaxZoom Jan 19 '15 at 19:13
  • My problem is not solved, I didn't formulate my question properly, so I've uploaded a new one (http://stackoverflow.com/questions/28152702/sql-conversation-chat-like-output?noredirect=1#comment44676761_28152702) – Steven Jan 26 '15 at 15:13

1 Answers1

1

If I understood your question properly, you need following query:

select t1.MESSAGEID,t1.MESSAGE, concat('from ',USERNAME) FROM_USER
from tblMsg t1 inner join 
    (select MAX(MESSAGEID) MESSAGEID,FROM_USERID
     from tblMsg
     group by FROM_USERID) t2 on t1.MESSAGEID=t2.MESSAGEID
    inner join tblUser u on t1.FROM_USERID=u.USERID

DEMO SQL Fiddle

Ravi Dhoriya ツ
  • 4,435
  • 8
  • 37
  • 48