0

Hi friends help me to solve this problem ," display all last messages of a user from all his/her friends"

My sample message table is

---------------------------------------------------
|  id | msg       | sender |  reciver | friend_id |
---------------------------------------------------
|  1  | Hi        | 1      | 2        | 1         |
|  2  | Hi..      | 2      | 1        | 1         |
|  3  | Hello     | 6      | 1        | 3         |
|  4  | What dong | 1      | 6        | 3         |
---------------------------------------------------

I need only the last message b/w the user and his friends (here consider user id is 1)

I need the result like below , please help me to achive this with mysql

---------------------------------------------------
|  id | msg       | sender |  reciver | friend_id |
---------------------------------------------------
|  4  | What dong | 1      | 6        | 3         |
|  2  | Hi..      | 2      | 1        | 1         |
---------------------------------------------------

The name of this table is message..

1
select a.id, a.msg, a.sender, a.receiver, a.friend_id
from msg a
left join msg b on (a.friend_id = b.friend_id and a.id < b.id)
where b.id is null
  and (a.sender = 1 or a.receiver = 1)
order by a.id desc;

Can be tested at http://sqlfiddle.com/#!9/35823/10

It uses "groupwise-max" solution with a left join as can be found for instance in mysql manual (last example).

Your schema seems a bit weird keeping sender, receiver and friend_id, but I suppose friend_id is a FK to some friends table where pairs of (1,2) and (1,6) are stored, so it is more of a "friendship_id" ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.