MySQL GROUP BY on Virtual Column with ORDER BY

Question

I've got a legacy DB structure inside messages for mysql 5.7 that we need to pull out "the most recent message between 2 users"

The structure looks basically like...

id | from_id | to_id | message | created_at (datetime)
-------------------------------------------
1  |    1    |   2   |   xxx   | 05:00
2  |    2    |   1   |   xxx   | 07:00
3  |    3    |   1   |   xxx   | 08:00
4  |    1    |   2   |   xxx   | 10:00

So assuming the above data, the result I'd like to get would be... (although just a list of IDs is fine)

id | from_id | to_id | message | created_at (datetime)
-------------------------------------------
3  |    3    |   1   |   xxx   | 08:00
4  |    1    |   2   |   xxx   | 10:00

As theres no concept of "conversations" it's hard to group the messages into orderable chunks, so I've created a virtual column which concats the 2 user id to make a fake conversation id to query within using:

select *, ANY_VALUE(CONCAT(LEAST(from_id, to_id), "-", GREATEST(from_id, to_id))) conversation from messages;

This gives me:

id | from_id | to_id | message | created_at | conversation
----------------------------------------------------------
1  |    1    |   2   |   xxx   | 05:00      |     1-2
2  |    2    |   1   |   xxx   | 07:00      |     1-2
3  |    3    |   1   |   xxx   | 08:00      |     1-3
4  |    1    |   2   |   xxx   | 10:00      |     1-2

So as you can see the conversation column now provides a way to group the messages consistently.

The next "logical" step would be to order by them by created_at then group by the conversation column.

SELECT *, ANY_VALUE(CONCAT(LEAST(from_id, to_id), "-", GREATEST(from_id, to_id))) conversation
FROM messages
WHERE from_id = 1 OR to_id = 1
GROUP BY conversation
ORDER BY created_at desc;

However those of you who know MySQL better than me... will know this won't work and it seems to group them by the AUTO_INC column.

What's the correct method to do this? (also keeping an eye out for the sneaky WHERE I added)

I've created an SQL Fiddle with a sample of data to demonstrate: http://sqlfiddle.com/#!9/4771d4/2/0

Thanks

user_10676716 · Answer

This is a greatest-n-per-group project: you want to filter rather than aggregate.

One option uses a subquery; least() and greatest() come handy to identify a conversation:

select m.*
from messages m
where m.id = (
    select m1.id
    from messages m1
    where 
        least(m1.from_id, m1.to_id) = least(m.from_id, m.to_id)
        and greatest(m1.from_id, m1.to_id) = greatest(m.from_id, m.to_id)
    order by created_at desc
    limit 1
)
order by created_at desc

Alternatively, if you are running MySQL 8.0, you can implement the same logic with window functions:

select *
from (
    select 
        m.*, 
        row_number() over(
            least(from_id, to_id), greatest(from_id, to_id)
            order by created_at desc
        ) rn
    from messages m
) t
where rn = 1
order by created_at desc

user_1863229 · Answer

Using ROW_NUMBER on MySQL 8+, we can try:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY LEAST(from_id, to_id),
                                              GREATEST(from_id, to_id)
                                 ORDER BY created_at DESC) rn
    FROM messages
)

SELECT id, from_id, to_id, message, created_at,
    CONCAT(LEAST(from_id, to_id), '-', GREATEST(from_id, to_id)) AS conversation
FROM cte
WHERE rn = 1;

On earlier versions of MySQL, an aggregation approach can be used:

SELECT m1.id, m1.from_id, m1.to_id, m1.message, m1.created_at,
    CONCAT(LEAST(m1.from_id, m1.to_id), '-',
           GREATEST(m1.from_id, m1.to_id)) AS conversation
FROM messages m1
INNER JOIN
(
    SELECT
        LEAST(from_id, to_id) AS from_id,
        GREATEST(from_id, to_id) AS to_id,
        MAX(created_at) AS max_created_at
    FROM messages
    GROUP BY
        LEAST(from_id, to_id),
        GREATEST(from_id, to_id)
) m2
    ON LEAST(m1.from_id, m1.to_id) = m2.from_id  AND
       GREATEST(m1.from_id, m1.to_id) = m2.to_id AND
       m1.created_at = m2.max_created_at;

MySQL GROUP BY on Virtual Column with ORDER BY

2 Answers