sql同时操作两列,SQL：如何按两列的唯一组合分组？

Context:

A table message has the columns from_user_id and to_user_id

The user should see the recent conversations with the last message displayed

A conversation consists of multiple messages, that have the same combination of user IDs (user sends messages, user receives messages)

Table content:

+-------------------------------------------------+--------------+------------+

| text | from_user_id | to_user_id |

+-------------------------------------------------+--------------+------------+

| Hi there! | 13 | 14 |

| Oh hi, how are you? | 14 | 13 |

| Fine, thanks for asking. How are you? | 13 | 14 |

| Could not be better! How are things over there? | 14 | 13 |

| Hi, I just spoke to Penelope! | 13 | 15 |

| Oh you did? How is she? | 15 | 13 |

| Liara told me you guys texted, how are things? | 15 | 14 |

| Fine, she's good, too | 14 | 15 |

+-------------------------------------------------+--------------+------------+

My attempt was to group by from_user_id and to_user_id, but I obviously get a group of the messages received by the user and another group of messages send by the user.

SELECT text, from_user_id, to_user_id,created FROM message

WHERE from_user_id=13 or to_user_id=13

GROUP BY from_user_id, to_user_id

ORDER BY created DESC

Gets me:

+-------------------------------+--------------+------------+---------------------+

| text | from_user_id | to_user_id | created |

+-------------------------------+--------------+------------+---------------------+

| Oh you did? How is she? | 15 | 13 | 2017-09-01 21:45:14 |

| Hi, I just spoke to Penelope! | 13 | 15 | 2017-09-01 21:44:51 |

| Oh hi, how are you? | 14 | 13 | 2017-09-01 17:06:53 |

| Hi there! | 13 | 14 | 2017-09-01 17:06:29 |

+-------------------------------+--------------+------------+---------------------+

Although I want:

+-------------------------------+--------------+------------+---------------------+

| text | from_user_id | to_user_id | created |

+-------------------------------+--------------+------------+---------------------+

| Oh you did? How is she? | 15 | 13 | 2017-09-01 21:45:14 |

| Oh hi, how are you? | 14 | 13 | 2017-09-01 17:06:53 |

+-------------------------------+--------------+------------+---------------------+

How can I achieve that?

EDIT:

Using least or greatest does not lead to the required results either.

It does group the entries correctly, but as you can see in the result, the last message is incorrect.

+----+-------------------------------------------------+------+---------------------+--------------+------------+

| id | text | read | created | from_user_id | to_user_id |

+----+-------------------------------------------------+------+---------------------+--------------+------------+

| 8 | Oh you did? How is she? | No | 2017-09-01 21:45:14 | 15 | 13 |

| 5 | Could not be better! How are things over there? | No | 2017-09-01 17:07:47 | 14 | 13 |

+----+-------------------------------------------------+------+---------------------+--------------+------------+

解决方案

One method of doing what you want uses a correlated subquery, to find the minimum created date/time for a matching conversation:

SELECT m.*

FROM message m

WHERE 13 in (from_user_id, to_user_id) AND

m.created = (SELECT MAX(m2.created)

FROM message m2

WHERE (m2.from_user_id = m.from_user_id AND m2.to_user_id = m.to_user_id) OR

(m2.from_user_id = m.to_user_id AND m2.to_user_id = m.from_user_id)

)

ORDER BY m.created DESC