I have a simple table like above for storing messages sent between 2 users.
I need to display one record for each ‘conversation’ that a user has, so in this example, user 33 has had 2 conversations:
conversation 1: one message sent by user 33 in record 1 to user 189 (no reply messages or other messages between the two users)
conversation 2: one message received by 33 in record 3 and one sent in record 4, both to user 63
I want to display the most recent message and show it like a header page where they click and view all messages between the two users.
I only need help on the query to retrieve the data from table above so I can display like below. I have everything else under control.
I am thinking GROUP BY, but how do I return the latest message (and time field) and how do I GROUP BY considering the user id could be in either the from_user_id or to_user_id field?
So I need to show the data like this in a header page (I just need help on the query that will sort the data to allow me to do this):
User 33’s messages:
Conversation number 1 (with user 189)
(date = 1334865983) Hello there (shows latest message)
Click HERE to View
Conversation number 2 (with user 63)
(date = 1334908903) Hi again (shows latest message)
SELECT m.with_user
, m.time
, m.message
FROM ( SELECT w.with_user
, MAX(w.time) AS max_time
FROM ( SELECT CASE WHEN t.from_user_id = 33
THEN t.to_user_id
ELSE t.from_user_id
END AS with_user
, t.time
FROM messages AS t
WHERE [COLOR="#FF0000"]33 [/COLOR]IN ( t.from_user_id , t.to_user_id ) ) AS w
GROUP
BY w.with_user ) AS x
INNER
JOIN messages AS m
ON m.time = x.max_time
AND ( m.from_user_id = [COLOR="#FF0000"]33 [/COLOR]and m.to_user_id = x.with_user
OR m.from_user_id = x.with_user and m.to_user_id = [COLOR="#FF0000"]33 [/COLOR])
If you are using SQL Server (2005 onwards) or Oracle you can look at the RANK or DENSE_RANK function partitioned by user ID and ordered by message datetime descending.
The approach would be a selecting from a derrived table including a column of RANK where the rank column equals 1.
I don’t think MySQL supports a RANK function, which is unfortunate as you will probably have to go with the much less efficient join on I’d with a MAX function.
Unknown column ‘m.with_user’ in ‘field list’
SELECT m.with_user , m.time , m.msg FROM ( SELECT w.with_user , MAX(w.time) AS max_time FROM ( SELECT CASE WHEN t.from_id = 22 THEN t.to_id ELSE t.from_id END AS with_user , t.time FROM messages_table AS t WHERE 22 IN ( t.from_id , t.to_id ) ) AS w GROUP BY w.with_user ) AS x INNER JOIN messages_table AS m ON m.time = x.max_time AND ( m.from_id = 22 and m.to_id = x.with_user OR m.from_id = x.with_user and m.to_id = 22 )
‘with_user’ isn’t a field in my table, but I’m pretty sure that it isn’t supposed to be based on the query you provided. The exact fields in my table are:
