mysql返回list有序吗,MySQL按发送/接收的最后一条消息获取联系人的有序列表

this is my situation,I have 2 tables, one about friends, another about messages.

Friends table is like this:

user_id|friend_id|accepted

12 | 1 | 1

13 | 1 | 1

1 | 3 | 1

accepted can be 0 or 1. (1 accepted, 0 nope)

Messages table

message|time |user_id|receiver_id

hi! | 1328688| 1 | 12

hey | 1343409| 12 | 1

Time is a timestamp, so i need to list in order by the highest timestamp for each friend.

I need to list all contacts (that are accepted = 1) in order of last message (send/received).

In other words, i need to group by sender and receiver, and take only one value (the last) from each friend.

The user "12" can be in user_id or friend_id, and in user_id and receiver_id...so i need to check both.

Someone can help me? Thanks guyz!

EDIT

my query:

SELECT * FROM friends,messages

WHERE (friends.user_id='$my_id'

OR friends.friend_id ='$my_id'

AND friends.accepted='1')

AND messages.user_id='$my_id'

OR messages.receiver_id='$my_id'

GROUP BY friends.friend_id

ORDER BY messages.time DESC");

I tried an INNER JOIN, but it's crazy guyz (i'm not so able with mysql)

EDIT 2

Message Table

Friend Table

EDIT 3 $my_id is variable for take my user_id

解决方案

Solved, this query worked for me

SELECT messages.* FROM messages, (SELECT MAX(id) as lastid

FROM messages

WHERE (messages.receiver_id = '$myid' OR messages.sender_id = '$myid')

GROUP BY CONCAT(LEAST(messages.receiver_id,messages.sender_id),'.',

GREATEST(messages.receiver_id, messages.sender_id))) as conversations

WHERE id = conversations.lastid

ORDER BY messages.time DESC