SQL: How do I group by a unique combination of two columns?

Question

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 | <- Liara to Penelope
| Oh hi, how are you?                             |           14 |         13 | <- Penelope to Liara
| Fine, thanks for asking. How are you?           |           13 |         14 | <- Liara to Penelope
| Could not be better! How are things over there? |           14 |         13 | <- Penelope to Liara
| Hi, I just spoke to Penelope!                   |           13 |         15 | <- Liara to Zara
| Oh you did? How is she?                         |           15 |         13 | <- Zara to Liara
| Liara told me you guys texted, how are things?  |           15 |         14 | <- Zara to Penelope
| Fine, she's good, too                           |           14 |         15 | <- Penelope to Zara
+-------------------------------------------------+--------------+------------+

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 | <- received by Liara
| Hi, I just spoke to Penelope! |           13 |         15 | 2017-09-01 21:44:51 | <- send by Liara
| 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 | <- Last message of conversation with Zara
| 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 |
+----+-------------------------------------------------+------+---------------------+--------------+------------+

Can we apply least to to_user_id and greatest to from_user_id and then group them — Chetan_Vasudevan, Sep 01 '17 at 20:46
Already tried, I edited the attempt to the question. Grouping works correct, but I don't get the last message — StoryTeller, Sep 01 '17 at 20:54
Those are intentional wrong columns, I only want one users messages: `WHERE from_user_id=13 or to_user_id=13` — StoryTeller, Sep 01 '17 at 20:59
So for user 13 you want all conversation partners (14 and 15) with the latest post? No matter whether #13 was the sender or the receiver? I understand why it is "Oh you did? How is she?" for #15. But why is it not "Could not be better! How are things over there?" for #14? — Thorsten Kettner, Sep 01 '17 at 21:05
You're right about that @Thorsten Kettner, I edited the question to show the correct desired results — StoryTeller, Sep 01 '17 at 21:36
Your idea what `GROUP BY` does is wrong by the way. It does get you one record per group (in your example `from_user_id, to_user_id`). It does not, however, magically choose the records/values you want for you. In `SELECT text, from_user_id, to_user_id, created`, you say you want to select a `text` for the group. But there are many. The DBMS is free to choose one and will arbitrarily do so. So for 13/14 you may get " Hi there!" or "Fine, thanks for asking. How are you?"; the DBMS is free to pick one. If you want a specific one, you need an aggregation function (`MAX`, `MIN` or the like). — Thorsten Kettner, Sep 01 '17 at 21:37
What you would need, however, is the text for the `max(created)` - an aggregation function that MySQL does not provide. (Oracle for instance does with `KEEP LAST`, but MySQL does not have such a function.) `ORDER BY created DESC` is executed at last, i.e. *after* `GROUP BY`. — Thorsten Kettner, Sep 01 '17 at 21:40

Gordon Linoff · Accepted Answer · 2017-09-02T08:23:58.463

2

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

edited Sep 02 '17 at 08:23

answered Sep 01 '17 at 21:00

Gordon Linoff

1,242,037
58
646
786

shouldn't it be MAX() since OP wants the latest message? – Ferdinand Gaspar Sep 01 '17 at 21:19
Yes, `MAX()` should be used, but to be fair I provided the wrong desired result in the question. – StoryTeller Sep 01 '17 at 21:23

Thorsten Kettner · Answer 2 · 2017-09-01T21:28:54.637

2

The last conversations with #13? In a more up-to-date DBMS you'd use row_number() to find these. In MySQL you can use not exists, to make sure that there is no later post for the conversation partners. You find the partner's number easily with from_user_id + to_user_id - 13 by the way. (And when comparing two records, you can just use from_user_id + to_user_id.)

select text, from_user_id, to_user_id, created
from message m1
where 13 in (from_user_id, to_user_id)
and not exists
(
  select *
  from message m2
  where 13 in (m2.from_user_id, m2.to_user_id)
  and m2.from_user_id + m2.to_user_id = m1.from_user_id + m1.to_user_id
  and m2.created > m1.created
);

edited Sep 01 '17 at 21:28

answered Sep 01 '17 at 21:14

Thorsten Kettner

89,309
7
49
73

You **CAN'T** compare id sums, `13 + 5 = 8 + 10` but are different conversations – Juan Carlos Oropeza Sep 01 '17 at 21:35
@Juan Carlos Oropeza: You are missing the `WHERE` clause. Your second pair doesn't contain a 13 and would hence not be selected. – Thorsten Kettner Sep 01 '17 at 21:42

Juan Carlos Oropeza · Answer 3 · 2017-09-01T21:16:25.767

I use GREATEST and LEAST to create a grp for each conversation. Then sort for that grp and assign a row number based on the time.

SQL DEMO

SELECT *
FROM (
        SELECT LEAST(`from_user_id`, `to_user_id`) as L,
               GREATEST(`from_user_id`, `to_user_id`) as G,
               `text`,
               CONCAT (LEAST(`from_user_id`, `to_user_id`), '-', GREATEST(`from_user_id`, `to_user_id`)) as grp,
               @rn := if(@grp = CONCAT(LEAST(`from_user_id`, `to_user_id`), '-', GREATEST(`from_user_id`, `to_user_id`)),
                         @rn + 1,
                         if(@grp := CONCAT(LEAST(`from_user_id`, `to_user_id`), '-', GREATEST(`from_user_id`, `to_user_id`)), 1, 1)
                         ) as rn,
               `time`
        FROM Table1
        CROSS JOIN (SELECT @rn := 0, @grp := '') as var
        ORDER BY LEAST(`from_user_id`, `to_user_id`),
                 GREATEST(`from_user_id`, `to_user_id`),
                 `time` DESC
     ) T
WHERE rn = 1;

OUTPUT

EDIT: at the end you need to filter the 13 from the conversation.

WHERE rn = 1
  AND 13 IN (`L`, `G`);

SQL: How do I group by a unique combination of two columns?

3 Answers3

Linked