List last messages of chats

Question

CREATE TABLE message
    (`id` int, `from_user_id` text, `to_user_id` text, `created` datetime, `message` text)
;

INSERT INTO message
    (`id`, `from_user_id`, `to_user_id`, `created`, `message`)
VALUES
    (1, 'a', 'b', '2013-01-14 00:00:00', 'hello'),
    (2, 'b', 'a', '2013-01-14 00:00:00', 'world'),
    (3, 'b', 'a', '2013-01-15 00:00:00', 'hi!!')
;

I want to get the last messages applicable to (either sent / received by ) a user. In the above example, there are only 2 users (a, b), and the last message applicable to them is id = 3. So the expected result is

'a', 3, 'b', 'a', '2013-01-15 00:00:00', 'hi!!'
'b', 3, 'b', 'a', '2013-01-15 00:00:00', 'hi!!'

There are similar questions in SO, the closest I found was here

The last answer seems to what I wanted, but it is complex to wrap my head around, and also couldn't make it work.

I use lovefield and run it in the browser. It supports join and subset of sql syntax, but may not advanced use. Below code gives last message for a provided user id, but I want rows for all users.

SELECT m.*
FROM message m
WHERE 'a' 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

sqlfiddle

Answer 1

Below code gives last message for a provided user id, but I want rows for all users.

Your query looks fine. Just remove the condition that filters on a specific user, and you should get the result that you expect:

SELECT m.*
FROM message m
WHERE 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

Here is an updated version of your fiddle. I added a few more records for other users, and the query returns:

id  from_user_id  to_user_id  created               message
4   d             c           2013-01-17T00:00:00Z  yo!!
3   b             a           2013-01-15T00:00:00Z  hi!!

Answer 2

Using UNION:

with lastmessages as
       (select id, from_user_id as user_id, from_user_id, to_user_id, max(created) mx, message
        from message group by(from_user_id) 
        UNION ALL
        select id, to_user_id as user_id,   from_user_id, to_user_id, max(created) mx, message
        from message group by(to_user_id) )
select user_id, id, from_user_id, to_user_id, max(mx) mx, message
  from lastmessages
  group by user_id;

Output

user_id|id|from_user_id|to_user_id|mx|message
a|3|b|a|2013-01-15 00:00:00|hi!!
b|3|b|a|2013-01-15 00:00:00|hi!!

Answer 3

Use a CTE and UNION ALL:

with cte as (
  select *,
    min(from_user_id, to_user_id) user1,
    max(from_user_id, to_user_id) user2,
    row_number() over (partition by min(from_user_id, to_user_id) order by created desc) rn1,
    row_number() over (partition by max(from_user_id, to_user_id) order by created desc) rn2
  from message
)
select user1 as user, id, from_user_id, to_user_id, created, message 
from cte
where rn1 = 1
union all
select user2 as user, id, from_user_id, to_user_id, created, message 
from cte
where rn2 = 1
order by user

See the demo.
Results:

| user | id  | from_user_id | to_user_id | created             | message |
| ---- | --- | ------------ | ---------- | ------------------- | ------- |
| a    | 3   | b            | a          | 2013-01-15 00:00:00 | hi!!    |
| b    | 3   | b            | a          | 2013-01-15 00:00:00 | hi!!    |