johnsmith153 — 2012-04-21T01:52:28-04:00 — #1
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
bfpon — 2012-04-21T02:12:33-04:00 — #2
What database are you using?
r937 — 2012-04-21T06:04:45-04:00 — #3
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>)
bfpon — 2012-04-21T07:41:38-04:00 — #4
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
johnsmith153 — 2012-04-21T21:26:57-04:00 — #5
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.
r937 — 2012-04-21T21:36:04-04:00 — #6
please show your query, and the error message
johnsmith153 — 2012-04-21T22:33:28-04:00 — #7
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:
bfpon — 2012-04-22T03:27:14-04:00 — #8
johnsmith153 — 2012-04-22T04:15:39-04:00 — #9
No that doesn't work either.
Thanks for looking though.
johnsmith153 — 2012-04-22T05:13:20-04:00 — #10
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.
r937 — 2012-04-22T07:58:12-04:00 — #11
problem is, this wasn't your error, it was mine!!!
mittineague — 2014-09-23T18:58:03-04:00 — #12
This topic is now archived. It is frozen and cannot be changed in any way.