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
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!!