primary_id / from_user_id / to_user_id / time / message
1 / 33 / 189 / 1334865983 / "Hello there"
2 / 44 / 76 / 1334897600 / "Hi Dave"
3 / 63 / 33 / 1334907887 / "Hello, message here"
4 / 33 / 63 / 1334908903 / "Hi again"
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)
Click HERE to View
What database are you using?
this is untested --
FROM ( SELECT w.with_user
, MAX(w.time) AS max_time
FROM ( SELECT CASE WHEN t.from_user_id = 33
END AS with_user
FROM messages AS t
WHERE <font color='"#FF0000"'>33 </font>IN ( t.from_user_id , t.to_user_id ) ) AS w
BY w.with_user ) AS x
JOIN messages AS m
ON m.time = x.max_time
AND ( m.from_user_id = <font color='"#FF0000"'>33 </font>and m.to_user_id = x.with_user
OR m.from_user_id = x.with_user and m.to_user_id = <font color='"#FF0000"'>33 </font>)
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.
Sent from my BlackBerry 9900 using Tapatalk
Thanks r937, but it has errors.
It keeps saying w.with_user isn;t a valid field. I then messed around and it started saying that m.with_user isn't a valid field.
please show your query, and the error message
Thanks for the reply. This is what is displayed:
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:
No that doesn't work either.
Thanks for looking though.
Sorry, my mistake, changing to 'x.with_user' works perfectly.
I had been messing around with other parts of the query and when I realised my error it worked straight away.
Thanks again to both.
problem is, this wasn't your error, it was mine!!!
This topic is now archived. It is frozen and cannot be changed in any way.