MySQL Query group by 获取最后一个id

Question

这是我的 table

id | sender | receiver | msg  
-----------------------------
1  | jon    | jack     | buzz ...  
2  | jack   | jon      | ?  
3  | adel   | jon      | met me soon  
4  | jon    | adel     | okay  
5  | alex   | jon      | where ar u ?  
6  | jon    | adel     | okay  
7  | adel   | alex     | don't forget the party  
8  | jon    | jack     | may i borrow ur car 
9  | alex   | adel     | of course
10 | jack   | jon      | ok
11 | jack   | jon      | watch the gas
12 | alex   | jon      | i'm dying here
13 | jon    | alex     | 5 mnt ..

我想像这样获取 jon 和他 friend 的最后消息列表

id | sender | receiver | msg
-------------------------------------------
13 | jon    | alex     | 5 mnt ..
11 | jack   | jon      | watch the gas
6  | jon    | adel     | okay

如何查询，得到那个结果？

Answer 1

使用这个:

SELECT *
FROM table
WHERE id IN (
    SELECT MAX(id)
    FROM table
    WHERE sender = 'jon' 
       OR receiver = 'jon'
    GROUP BY IF(sender = "jon", receiver, sender)
) 
ORDER BY id DESC;

编辑:感谢ypercube的编辑，我真的忘记了一些重要的东西:)