CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Steven
Steven

Reputation: 1173

SQL distinct latest post

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

Upvotes: 1

Views: 73

Answers (1)

Ravi Dhoriya ツ
Ravi Dhoriya ツ

Reputation: 4414

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

Upvotes: 1

Related Questions