MySQL:按两列分组而不考虑顺序

Question

我有一个表格，就像短信一样，它有 from、to、message 和 time 列(除其他外)。我正在尝试询问两个数字(from 和 to)之间的最新记录，不考虑方向。

这是表格的一个例子:

+--------------+-----------------+--------------------+--------------+
| from         | to              | message            | time         |
+--------------+-----------------+--------------------+--------------+
| 555-1234     | 555-9876        | I'll be there.     | 06:00        |
| 555-9876     | 555-5555        | message3           | 05:30        |
| 555-9876     | 555-1234        | Bring beer         | 05:00        |
| 555-9876     | 555-1234        | My place at 8pm    | 04:00        |
| 555-9876     | 555-5555        | message2           | 03:45        |
| 555-5555     | 555-9876        | message1           | 03:30        |
| 555-9876     | 555-1234        | Are you coming?    | 03:00        |
| 555-1234     | 555-9876        | Yeah, what's up?   | 02:00        |
| 555-9876     | 555-1234        | Are you there?     | 01:00        |
+--------------+-----------------+--------------------+--------------+

我只想从此示例中获取以下记录:

+--------------+-----------------+--------------------+--------------+
| from         | to              | message            | time         |
+--------------+-----------------+--------------------+--------------+
| 555-1234     | 555-9876        | I'll be there.     | 06:00        |
| 555-9876     | 555-5555        | message3           | 05:30        |
+--------------+-----------------+--------------------+--------------+

我如何要求这些？

Answer 1

你可以使用带最大值和元组的子查询

select * from my_table 
where (from, to, time) in ( select from, to, max(time) 
                            from my_table
                            group by from, to)